Selecting an Incrementing Value in MySQL

Warning - Old Content

This post is quite old, and it might not apply anymore, or maybe there's a better way to do the same thing nowadays. Take with a big grain of salt.

Sometimes you need to add a row number to the results of a query. Oracle provides a useful ROWNUM function to do that easily, but in mysql, you have to use user variables:

SET @count=0;  
SELECT name,@count:=@count+1 FROM foo;  

That’s alright, but then what do you do if you want to insert those numbers into a table? No problem, you can do that with a subselect:

SET @count=0;  
UPDATE mytable SET ord = (SELECT @count:=@count + 1);  

You can use conditions on the update as well, which is very handy if you’re trying to stick together the data from 2 tables based on the row order.