Increment autoincrement id field by one
auto-increment, database, increment, mysql, sql
Solution
I see no good reason for this. Only problems. Before running the folowing statement, check if you have `FOREIGN` keys defined, that reference this `id`. Are they set to `ON UPDATE CASCADE`? Also, do you have any triggers that are related to this table?
But first consider, why you (think you) need this. Is it going to be used for ordering the table? In that case, as @Mark pointed, you should use a separate column to specify your desired order.
If, however, you decide you really want this, use:
UPDATE myTable
SET id = id + 1
WHERE id >= 53
ORDER BY id DESC ;
Problem
I have a MySQL 5 server and a table in it with an autoincrement on an id field (primary key). Now I want to add a record in between and so I have to increase all other ids by one. This is what I tried: ``` UPDATE myTable SET id=id+1 WHERE id >= 53 ``` This doesn't work because for example a record with id = 52 already exists. How can I do this? If he would start at the last entry and makes the updates it should work I think. But how?