CODEWITHSUNDEEP
sqlphpmyadmin
Howli
Howli

Reputation: 12469

Add auto_increment value to table with existing data

I have a table with around 400 rows in it on phpmyadmin. I have added an id column and it is the primary key and is an auto increment. How can I update the table with sql and add a value for each row in the id column without ? I was trying to do it with something like

WHILE (SELECT COUNT(*) FROM cong WHERE 'ID' IS NULL) > 0
BEGIN
    SET ROWCOUNT 1
    UPDATE cong SET 'ID' = MAX('ID')+1
END

but that comes back with an error:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE (SELECT COUNT(*) FROM cong WHERE 'ID' IS NULL) > 0 BEGIN SET ROWCOUN' at line 1

How can I achieve adding the auto increment number into each row?

Upvotes: 1

Views: 1269

Answers (1)

Isaac Bennetch
Isaac Bennetch

Reputation: 12422

If I understand correctly, you originally did not have the id column, inserted the ~400 rows, and then decided you needed an id and added the column, setting it as a primary key and auto_increment. Now you want to go back and add values to each of the ~400 rows which did not previously have an ID.

When you added the primary key and auto_increment, it should have created those auto_increment values for you. I just did a test with several rows of data (a single varchar column), then through phpMyAdmin (since you tagged your post with phpmyadmin) I added a new column id of type INT, checking the A_I (auto_increment) checkbox and selecting PRIMARY as the key. Browsing the data shows that my existing rows each have an ID.

Are you saying your column is a primary key with auto_increment set and the field is blank? It doesn't make sense that MySQL would allow that. If that's true, can you post your table structure?

Upvotes: 1

Related Questions