How to swap values of two rows in MySQL without violating unique constraint?

Is it possible to accomplish this in MySQL without using bogus values and multiple queries?

No. (none that I can think of).

The problem is how MySQL processes updates. MySQL (in difference with other DBMS that implement UPDATE properly), processes updates in a broken manner. It enforces checking of UNIQUE (and other) constraints after every single row update and not – as it should be doing – after the whole UPDATE statement completes. That’s why you don’t have this issue with (most) other DBMS.

For some updates (like increasing all or some ids, id=id+1), this can be solved by using – another non-standard feature – an ORDER BY in the update.

For swapping the values from two rows, that trick can’t help. You’ll have to use NULL or a bogus value (that doesn’t exist but is allowed in your column) and 2 or 3 statements.

You could also temporarily remove the unique constraint but I don’t think that’s a good idea really.

So, if the unique column is a signed integer and there are no negative values, you can use 2 statements wrapped up in a transaction:

    UPDATE tasks 
    SET priority = 
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;

Leave a Comment