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

Sure, begin a transaction, lock the table, drop the constraint make the change add the constraint back commit the transactions then unlock the table. — xQbert, Jun 26 '12 at 13:10

ypercubeᵀᴹ · Accepted Answer · 2013-11-15T17:55:59.960

41

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 ;

edited Nov 15 '13 at 17:55

answered Jun 26 '12 at 13:05

ypercubeᵀᴹ

113,259
19
174
235

Good explanation. Now I'm happy to use bogus values! – wannabeartist Jun 26 '12 at 13:37
3

it's a terrific answer... albeit NOT the one I wanted to hear! What a royal PITA! – Yevgeny Simkin Jul 27 '12 at 07:20
What do I do if these values are strings instead of integers? – decached Nov 15 '13 at 18:00
@AkashKothawale You could use a value that doesn't exist in the column or it has very low probability to be there, like: `'ZZZZZ$$$$$ZZZZZ$$$$$'` for the intermediate step. – ypercubeᵀᴹ Nov 15 '13 at 18:02
@ypercube Manipulating the intermediate string is quite different that just negating an INT. Any workaround? – decached Nov 15 '13 at 18:10
@AkashKothawale I posted an answer in your question. – ypercubeᵀᴹ Nov 15 '13 at 22:29
1

@ypercube, What do I do if the column `priority` has foreign key constraint and the -ve values of the existing values (in this case -2 and -3) don't exist in the referenced table? – decached Mar 12 '14 at 11:30
1

@AkashKothawale is the column nullable? – ypercubeᵀᴹ Mar 12 '14 at 11:32
@ypercube, No it isn't. – decached Mar 12 '14 at 13:02

score 2 · Answer 2 · answered May 05 '16 at 03:54

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.

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.
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;

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.

score 1 · Answer 3 · answered Mar 10 '17 at 09:53

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.

score 1 · Answer 4 · answered Sep 23 '20 at 08:12

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:

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

hex494D49 · Answer 5 · 2023-08-18T12:40:03.970

0

There is another fairly straightforward way to swap table rows if your target column is not a unique constraint.

UPDATE `tasks` AS a
INNER JOIN `tasks` AS b ON a.id <> b.id
  SET a.priority = b.priority
WHERE a.id IN (2,3) AND b.id IN (2,3)

edited Aug 18 '23 at 12:40

answered Apr 15 '23 at 22:48

hex494D49

9,109
3
38
47

1

This does not work with MySQL, it throws `Duplicate entry '3' for key 'priority_UNIQUE'` – Honza Aug 18 '23 at 08:32
@Honza I just checked the solution and since you're right I changed the description. I'm not deleting it because It still might be helpful to someone else. – hex494D49 Aug 18 '23 at 12:42

David dB · Answer 6 · 2014-12-17T13:42:28.200

-2

Not sure if this would violate the constraints, but I have been trying to do something similar and eventually came up with this query by combining a few of the answers I found:

UPDATE tasks as T1,tasks as T2 SET T1.priority=T2.priority,T2.priority=T1.priority WHERE (T1.task_id,T2.task_id)=($T1_id, $T2_id)

The column I was swapping did not use a unique, so I am unsure if this will help...

edited Dec 17 '14 at 13:42

answered Dec 17 '14 at 13:30

David dB

69
1
8

score -3 · Answer 7 · edited Jun 26 '12 at 15:38

-3

you can achieve swapping your values with your above mentioned update statement, with a slight change in your key indexes.

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

This will have a primary key index as a combination of id and priority. you cna then swap values.

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

WHERE priority IN (2,3);

I dont see any need of user variables or temp variables here. Hope this solves your issue :)

edited Jun 26 '12 at 15:38

Martin Smith

438,706
87
741
845

answered Jun 26 '12 at 13:51

satdev86

800
7
14

4

But that's not enforcing the same constraint as the original - you might as well have just deleted the `UNIQUE KEY` constraint, and left the `PRIMARY KEY` constraint alone. – Damien_The_Unbeliever Jun 26 '12 at 14:49
1

Yes, this far from a "slight" change. It even allows duplicate values in the `id` column. – ypercubeᵀᴹ Nov 15 '13 at 18:04

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

7 Answers7

Linked