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.