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

Question

I have a "tasks" table with a priority column, which has a unique constraint.

I'm trying to swap the priority value of two rows, but I keep violating the constraint. I saw this statement somewhere in a similar situation, but it wasn't with MySQL.

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

This will lead to the error:

Error Code: 1062. Duplicate entry '3' for key 'priority_UNIQUE'

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

EDIT:

Here's the table structure:

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `priority` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `priority_UNIQUE` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

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 ; 

Answer 2

I bumped into the same issue. Had tried every possible single-statement query using CASE WHEN and TRANSACTION - no luck whatsoever. I came up with three alternative solutions. You need to decide which one makes more sense for your situation. In my case, I'm processing a reorganized collection (array) of small objects returned from the front-end, new order is unpredictable (this is not a swap-two-items deal), and, on top of everything, change of order (usually made in English version) must propagate to 15 other languages.

1. 1st method: Completely DELETE existing records and repopulate entire collection using the new data. Obviously this can work only if you're receiving from the front-end everything that you need to restore what you just deleted.

2. 2st method: This solution is similar to using bogus values. In my situation, my reordered collection also includes original item position before it moved. Also, I had to preserve original index value in some way while UPDATEs are running. The trick was to manipulate bit-15 of the index column which is UNSIGNED SMALLINT in my case. If you have (signed) INT/SMALLINT data type you can just invert the value of the index instead of bitwise operations.

First UPDATE must run only once per call. This query raises 15th bit of the current index fields (I have unsigned smallint). Previous 14 bits still reflect original index value which is never going to come close to 32K range.

UPDATE *table* SET `index`=(`index` | 32768) WHERE *condition*; 

Then iterate your collection extracting original and new index values, and UPDATE each record individually.

foreach( ... ) {     UPDATE *table* SET `index`=$newIndex WHERE *same_condition* AND `index`=($originalIndex | 32768); } 

This last UPDATE must also run only once per call. This query clears 15th bit of the index fields effectively restoring original index value for records where it hasn't changed, if any.

UPDATE *table* SET `index`=(`index` & 32767) WHERE *same_condition* AND `index` > 32767; 

3. Third method would be to move relevant records into temporary table that doesn't have a primary key, UPDATE all indexes, then move all records back to first table.

Answer 3

Bogus value option:

Okay, so my query is similar and I've found a way to update in "one" query. My id column is PRIMARY and position is part of a UNIQUE group. This is my original query that doesn't work for swapping:

INSERT INTO `table` (`id`, `position`)
  VALUES (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

.. but position is an unsigned integer and it's never 0, so I changed the query to the following:

INSERT INTO `table` (`id`, `position`)
  VALUES (2, 0), (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

.. and now it works! Apparently, MYSQL processes the values groups in order.

Perhaps this would work for you (not tested and I know almost nothing about MYSQL):

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=3 THEN 0 
    WHEN priority=2 THEN 3 
    WHEN priority=0 THEN 2 
END 

WHERE priority IN (2,3,0);

Good luck.

Answer 4

Had a similar problem.

I wanted to swap 2 id's that were unique AND was a FK from an other table.

The fastest solution for me to swap two unique entries was:

1. Create a ghost entry in my FK table.
2. Go back to my table where I want to switch the id's.
3. Turned of the FK Check SET FOREIGN_KEY_CHECKS=0;
4. Set my first(A) id to the ghost(X) fk (free's A)
5. Set my second (B) id to A (free's B)
6. Set A to B (free's X)
7. Delete ghost record and turn checks back on. SET FOREIGN_KEY_CHECKS=1;