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.

4 thoughts on “Assign a consecutive number to each row in a mySQL table”

Comments are closed.