Geek Answers Handbook

Increment auto increment identifier field one at a time

I have a MySQL 5 server and a table with it with auto-increments in the id field (primary key). Now I want to add an entry between them, and so I need to increase all the other identifiers by one. This is what I tried:

UPDATE myTable SET id=id+1 WHERE id >= 53

This does not work because, for example, an entry with id = 52 already exists. How can i do this? If he starts with the last record and makes updates, this should work, I think. But how?

+5
source share
4 answers

. . folowing, , FOREIGN , id. ON UPDATE CASCADE? , , ?

, , (, ) . ? , @Mark, , .

, , , , :

UPDATE myTable 
SET id = id + 1 
WHERE id >= 53
ORDER BY id DESC  ;
+11

, 2 .

  • id ,
  • .

UPDATE myTable SET id=id+10000 WHERE id >= 53
UPDATE myTable SET id=id-9999 WHERE id >= 53
+4

, - :

UPDATE myTable SET id=id+1 WHERE id >= (select id from myTable order by id DESC)
0

. OP @Romain Hoog. .

Finally, he exported the entire table data to excel and did it in excel (not one by one, but using a trick that does it very quickly).

after which a backup copy of the original table was made, an updated new table and imported data from the updated excel.

0
source

More articles:


All Articles