How to update a table to add a primary key and update all existing rows with incremental identifiers?

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 line to start with ID 1 and increase up to the last line and end with ID 20000. How do I update all rows with a single request?

I am using MySQL. Tried to use PhpMyAdmin, but he didn’t.

Answer 1

After adding a new identifier column (do not set as primary key yet and do not enable auto-increment):

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

This sets the value of the incremental identifier, starting from 1 on each of your existing rows, thereby eliminating the duplicate key problem you would encounter if you tried to insert a new primary key column after the data has already been entered.

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

Answer 2

Change tablename and run the following SQL statement. It will create a new primary key called id and populate it, starting from the first line, as 1.

 ALTER TABLE `tablename` ADD COLUMN id INT(10) AUTO_INCREMENT NOT NULL FIRST, ADD PRIMARY KEY (id);