Increment autoincrement id field by one

Question

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?

Answer 1

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  ;