Assign a consecutive number to each row in a mySQL table

Say that you need to add a unique index to an existing catalog table, so you decide to add a new column with a consecutive number in it. You may make it an auto_increment column, but that would work only for new inserts. How would you populate the column for the exisiting rows?
If you’re using mySQL you may try this little recipe:
First let’s add the column that we’ll use later as primary key.

alter table mytable add column id integer first;

Now we declare a counter and use it to populate each row.

set @i = 0;
update mytable set id=(@i:=@i+1);

Finally, we transform the new column into the primary key.

alter table mytable modify column id integer auto_increment primary key;

There you go. A new auto-increment primary key that has been correctly populated for all existing rows.

Posted in General
4 comments on “Assign a consecutive number to each row in a mySQL table
  1. Very helpful! Thanks for the information. Big question, though: Could you make this work for multiple tables?

  2. cedrick says:

    And this

  3. Eda says:

    Thank you for sharing this! Exactly what I was looking for.

  4. jadi says:

    Heh. That’s sliiiiick!