ProblemYou resequenced a column, but MySQL didn't number the rows the way you want. SolutionSelect the rows into another table, using an ORDER BY clause to place them in the order you want, and let MySQL number them as it performs the operation. Then the rows will be numbered according to the sort order. DiscussionWhen you resequence an AUTO_INCREMENT column, MySQL is free to pick the rows from the table in any order, so it won't necessarily renumber them in the order that you expect. This doesn't matter at all if your only requirement is that each row have a unique identifier. But you might have an application for which it's important that the rows be assigned sequence numbers in a particular order. For example, you may want the sequence to correspond to the order in which rows were created, as indicated by a TIMESTAMP column. To assign numbers in a particular order, use this procedure:
An alternative procedure:
|