When you have an “position” column in a table, to allow the user to select the order of elements in the table or as an optimization for pagination. They always seem to get out of sequence at some point—ending up with gaps in the sequence. This is something I run into not quiet often enough to remember how to do it, yet often enough to to be frustrating. So here is an easy solution to re-sequence the position column in a table, maintaining the current order, just closing any gaps.

SET @pos := 0; UPDATE Example SET position = @pos := @pos + 1 ORDER BY position

It simply initializes a variable (@pos) to 0, then for each row (updates are done in sequence) increments the variable and assigns it to the position column. The ORDER BY clause ensures the current ordering is maintained. WHERE clauses can also be added as required.