How can I set a counter column value in MySQL?

Posted by Jon Tackabury on Stack Overflow See other posts from Stack Overflow or by Jon Tackabury
Published on 2010-03-27T03:04:01Z Indexed on 2010/03/27 3:13 UTC
Read the original article Hit count: 339

Filed under:
|
|

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. :)

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql