CODEWITHSUNDEEP
mysqlsql
Kakawait
Kakawait

Reputation: 4029

SQL regenerate ID to keep autoincrement

I'm looking for pure SQL (or MySQL) syntax and I don't know if it's possible else I will create an php script but ...

I have a basic table with one primary key : ID and somes columns. I have no dependance with others tables.

At the moment my rows look like :

ID   Column1   Column2  
22   test      test
26   test2     test2
33   test3     test3
...

Now I want to regenerate all my ID to keep order. Example: I set start at 22 

ID   Column1   Column2  
22   test      test
23   test2     test2
24   test3     test3
...

Upvotes: 4

Views: 5448

Answers (2)

Code Magician
Code Magician

Reputation: 24022

The easiest approach would be to create a new table with your desired auto_increment column then 

INSERT INTO myNewTable (column1, column2)
SELECT column1, column2
FROM myOldTable
ORDER BY ID

Upvotes: 1

yokoloko
yokoloko

Reputation: 2860

Go and look there : Reorder / reset auto increment primary key

But as said there it will ruined the relationship you already have. But since you don't it's ok

ALTER TABLE `table` DROP `id`;
ALTER TABLE `table` AUTO_INCREMENT = 1;
ALTER TABLE `table` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Upvotes: 13

Related Questions