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:
START TRANSACTION ; UPDATE tasks SET priority = CASE WHEN priority = 2 THEN -3 WHEN priority = 3 THEN -2 END WHERE priority IN (2,3) ; UPDATE tasks SET priority = - priority WHERE priority IN (-2,-3) ; COMMIT ;