mysql - sql loop and set properties

Question

In SQL, I would like to query a list, in order by pageNumber

SELECT * FROM `comics` 
WHERE 1
ORDER BY pageNumber ASC

Then, I would like to set their pageNumbers based on their index in the query (starting with 1 instead of 0).

Here is my pseudo code for the functionality as desired; where list is the return value of the Select Query above.

for(var n:int = 0; n<list.length; n++){
    if(list[n].pageNumber != n+1){
        list[n].pageNumber = n+1
    }
}

For example I might have pageNumbers 5, 17, 23, 24, 18, 7

The ORDER BY pageNumber ASC will sort this to 5, 7, 17, 18, 23, 24

I would then like to alter the pageNumbers in order to be 1, 2, 3, 4, 5, 6

edit:

@fortheworld MySQL

@cyberkiwi UPDATE

sorry for being unclear. guess i need to learn more for my questions to be clear :) thanks for all your help

Answer 1

SET @I := 0;

  SELECT *,
         @I := @I + 1 AS newPageNumber
    FROM comics
ORDER BY pageNumber ASC