mysql sort field increment value

Question

Say I have a table

name             rank
-----------------------
John             1
Tit              3
Bernard          4

Rank 2 is missing, could have been deleted or whatever. I need a query to increment the rank field. So John would be number 1, but Tit would now be number 2, and Bernard 3.

There could be anywhere up to 100 ranks, and several missing. As long as the smallest rank is reset to number 1, and all that follow increment, it should be good.

Any ideas?

A query to update the rank field.

Answer 1

This will update the rank field so that it increments without holes:

SET @i := 0;
UPDATE tbl SET rank = @i:=@i+1 ORDER BY rank;