How do I update a table to add a primary key and update all of the existing rows with incremented IDs?

Question

I have a table with 20,000 rows of data that I imported but I forgot to put a primary key on it so that each row has a unique key.

I want the first row to start at ID 1 and increment all the way up to the last row and finish at ID 20000. How do I update all of the rows with a single query?

I'm using MySQL. Have tried using PhpMyAdmin but it wouldn't do it.

Answer 1

After adding a new ID column (don't set as a primary key just yet, and don't turn on auto increment) run:

SET @index = 1;
UPDATE tablename SET ID = (@index:=@index+1);

This sets an incrementing ID value starting from 1 onto each of your existing rows thus solving the duplicate key issue you would face if you tried to insert a new primary key column after data has been already entered.

Once this is done you can set the ID column as a primary key with auto increment.