Using ORDER BY with UPDATE to resolve the duplicate key problem

Indrek Siitan

To illustrate the problem, let us create a small sample table:

CREATE TABLE items (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32)
);

Let's add a couple of rows as well:

INSERT INTO items VALUES (1,'bar'),(2,'qux');

Now, we want to add an entry named "foo" with an ID of 1, and shift all of the existing items up by one. Easy, you say - just add 1 to the id field values and then insert the new row:

UPDATE items SET id=id+1;
INSERT INTO items VALUES (1,'foo');

In theory, everything looks fine. In real life, however, the UPDATE query most probably bails out with the following error:

ERROR 1062: Duplicate entry '2' for key 1

This happens because key constraints are checked before updating each row (with MyISAM tables that are non-transactional, there's no other way, and for some reason InnoDB follows the same path). The update is done in the order the rows are stored in the table files, which, in case you haven't mixed DELETEs and INSERTs and caused the table to get fragmented, is the order you have inserted the rows into the table. So if MySQL starts to process the first row and tries to increment the id field by 1, the result of 2 already exists in the table and produces the error above, although it would be increased next and the final query result would not violate the key uniqueness.

But not to worry, MySQL has extended the UDPATE syntax with a possible ORDER BY clause, that will help us solve this problem. If we change the update query to:

UPDATE items SET id=id+1 ORDER BY id DESC;

With this ORDER BY clause, MySQL processes rows in descending order of id number: First, it updates 2 to 3, and then 1 to 2. Consequently, no duplicate-key violation occurs and the statement succeeds.

The ORDER BY clause in UPDATE is available in MySQL server version 4.0.0 and newer.