SQL regenerate ID to keep autoincrement

Question

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
...

possible duplicate of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) — gbn, Aug 09 '11 at 08:10
Side note: `AUTO_INCREMENT` is not a good choice for identifiers that do have a meaning. For instance, you have to issue a DML query just to reset it. — Álvaro González, Aug 09 '11 at 08:14

score 12 · Accepted Answer · edited May 23 '17 at 12:07

12

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;

edited May 23 '17 at 12:07

Community

1
1

answered Aug 09 '11 at 08:07

yokoloko

2,820
3
20
27

btw first google answer for "reorder auto increment", which is faster to type than ask the question ??? – yokoloko Aug 09 '11 at 08:09
How does this guarantee the order is kept? – Álvaro González Aug 09 '11 at 08:19
It doesn't. But it wasn't the question. And since there is no relationship it's ok. – yokoloko Aug 09 '11 at 08:20
"I want to regenerate all my ID to keep order". Never mind, he seems happy with the answer :) – Álvaro González Aug 09 '11 at 08:22
To keep order in this sentence means to keep the numbers ordered as in 1,2,3,4,5,6,7,8,9,10 (at least it's how I understand it) – yokoloko Aug 09 '11 at 08:24

score 1 · Answer 2 · answered Aug 09 '11 at 08:09

1

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

answered Aug 09 '11 at 08:09

Code Magician

23,217
7
60
77

SQL regenerate ID to keep autoincrement

2 Answers2