Swap values of two rows in MySQL without violating unique constraint?

Question

I tried this code

UPDATE testing_table t1
INNER JOIN testing_table t2 ON (t1.id, t2.id) IN ((1, 2),(2, 1))
SET t1.emp_id = t2.emp_id

but seems to have this error

#1062 - Duplicate entry '3' for key 'emp_id'

Let me know how to swap 2 column vales without violating unique constraints in mysql

Duplicate? http://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint — L00_Cyph3r, May 11 '16 at 09:01
yes. but the values are unknown. emp_id is unique and swap between 2 ids. — Steffi, May 11 '16 at 09:07
Can I ask why you need to do this? primary key values are not supposed to change... If you need to swap, then swap all the other field values of these two records. — trincot, May 11 '16 at 09:54
I need to swap the unique emp_id values where id in (1,2) not the primary key id itself — Steffi, May 11 '16 at 09:59
(1) Do you have a foreign key constraint on the column *emp_id*? (2) Is the column allowed to have `null` values? — trincot, May 11 '16 at 12:11
yes there is foreign key constraint on the column emp_id and null values are not allowed. @trincot — Steffi, May 11 '16 at 12:26
Is a solution acceptable where you have to allow nulls (temporarily)? — trincot, May 11 '16 at 12:37
Is a solution acceptable which would temporarily insert a record in your referenced employee table, removing it once the swap is done? — trincot, May 11 '16 at 12:40
Thanks for your response, if that's the case then what if the table already contains null? then entry of another 0 would give duplicate entry? In my case its already existing huge table and I wanna also swap email ids wherein email ids can be null. — Steffi, May 11 '16 at 12:44
Nulls are not considered duplicates, even if you have many of them. Does that mean you would allow nulls for *emp_id*, even if temporarily? — trincot, May 11 '16 at 12:48
Sorry for the confusion, I have removed. Assume emp_id and email are not foreign constraint and I also removed all null values from db. Please provide the code for this. But I tried to make the email and emp_id fields nulls where null already exists, then an error #1062 - Duplicate entry '' for key 'email' is shown and same for emp_id — Steffi, May 11 '16 at 12:58
For the duplicate key error, please provide your code (`create table ...`, `insert ...`) that triggers the error, because you cannot get a duplicate entry just because you insert a `null`. It might be a message about another column. — trincot, May 11 '16 at 13:07
'CREATE TABLE `testing_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `emp_id` int(11) NOT NULL, `email` varchar(250) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `emp_id` (`emp_id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1' — Steffi, May 11 '16 at 13:12
This is the query UPDATE `testing_table` SET `email` = '' WHERE `testing_table`.`id` =4 This is the error message - #1062 - Duplicate entry '' for key 'email' — Steffi, May 11 '16 at 13:14
Note that `''` is the empty string, which is not the same as `null` (without quotes). Alter your table to allow `null`, and then try to insert multiple `null` values. It will be allowed. — trincot, May 11 '16 at 13:34

score 3 · Accepted Answer · answered May 11 '16 at 13:42

In MySql this is not easy to do, as it checks the unique constraints for every individual record during the update, not at the end (only).

So in order to allow the values of a column to be swapped, you need to allow the column to get a temporary value that will not conflict with any constraint. So if you have a column that has a foreign key constraint, and does not allow null values either, it is going to be hard to find such a value in all possible cases.

For the following solution null values must be allowed. If they currently are not, then first issue this statement to allow null for the emp_id column:

alter table testing_table modify column emp_id int null;

Then:

start transaction;
    update testing_table
       set emp_id = null
    where  id = 1 and if(@emp1 := emp_id, 1, 1)
       or  id = 2 and if(@emp2 := emp_id, 1, 1);

    update testing_table
       set emp_id = if(id = 1, @emp2, @emp1)
    where  id in (1, 2);
commit;

SQL fiddle.

Explanation

This will first set the values to null, while storing their previous values in @emp1 and @emp2, and updates the same records with the values as retained in these variables, but swapped.

The ifs in the first update statement are just there to make sure the assignment returns a true expression and make the where condition succeed. Note also that short-circuit evaluation will make sure that those assignments only happen when the left side of the and operator evaluated to true.

If an exception is raised, you can detect and handle that via a handler and rollback. See this [question & answer](http://stackoverflow.com/questions/19905900/mysql-transaction-roll-back-on-any-exception) for how to do this. If there was no exception, there is nothing you can do. :-( What exactly do you mean with *"doesn't work"*? — trincot, May 12 '16 at 12:41

score 0 · Answer 2 · edited May 11 '16 at 11:29

0

Please try with this one. It should work for you :)

START TRANSACTION;
    UPDATE testing_table 
    SET emp_id = 
      CASE
        WHEN emp_id = 1 THEN -2 
        WHEN emp_id = 2 THEN -1 
      END 
    WHERE emp_id IN (1,2);

    UPDATE testing_table 
    SET emp_id = - emp_id
    WHERE emp_id IN (-1,-2);
COMMIT;

edited May 11 '16 at 11:29

trincot

317,000
35
244
286

answered May 11 '16 at 09:10

Kamruzzaman Ripon

26
3

thanks for your code, but i need to swap emp_id values where id in (1,2) – Steffi May 11 '16 at 09:58
This code (as of now) assumes your emp_id column is not of type INT UNSIGNED, because unsigned columns do not accept negative values and will replace them with 0. – Anton May 11 '16 at 11:31
yes @Anton. Can you help me with the code so that unique emp_id are swapped between 2 rows based on primary key id? – Steffi May 11 '16 at 11:41

Swap values of two rows in MySQL without violating unique constraint?

2 Answers2

Explanation