How can I set a counter column value in MySQL?

Question

I have a table with a "SortID" column that is numbered using consecutive numbers. Whenever a row is deleted, it leaves a gap. Is there a way using pure SQL to update the rows with their row number? Something like this:

UPDATE tbl SET SortID={rowindex} ORDER BY SortID

(I realize this isn't valid SQL, that's why I'm asking for help)

This should set the first row to #1, the second row to #2... etc. Is this possible using SQL? Please forgive the poorly worded question, I'm not really sure the best way to ask this. :)

Answer 1

You can use mysql user defined variables

SET @pos=0;
SELECT @pos:=@pos+1, * FROM tbl ORDER BY SortID;

Answer 2

MySQL variables can be used for this.

SET @a:=0;
UPDATE tbl SET sortId=@a:=@a+1 ORDER BY sortId;

Answer 3

Are you sure you need it? Presentation layer is used for numbering most of time.