MySQL Update two rows in one atomic action

Question

I've got a table id a PK ID and a varchar(20) with some text. That PK is also a FK in another table I'm trying to switch the ID between two rows but I've been unable to do so as MySQl prevents this with an error with the FK constrain as for atomically, that row will violate the FK constrain but only for a milisecond as I will make that ID exist again with the other row. for example

Table 1

ID varchar(10)
1 "Hello"
2 "Bye"

Table 2

1(PK,FK) "Another data"
2(PK,FK) "BLA"

If I do

UPDATE Table 1 set ID=1 where text="Bye";
UPDATE Table 1 set ID=2 where text="Hello";

It will fail as

ID already exist
It will violate the FK constrain.

How can I do this?

EDIT: I have to do this once but for a bunch of rows as someone didn't add some data that needs to be in a specific index. Using an intermediate value will fix 1 but will still violate the FK constrain. The posted answer doesn't talk about FK.

Is this something that is will need to be done often or just a rare data fix? If so you may want to review why you are having to do this. — SQLChao, Jun 07 '16 at 18:26
Possible duplicate of [Swap unique indexed column values in database](http://stackoverflow.com/questions/644/swap-unique-indexed-column-values-in-database) — Edu, Jun 07 '16 at 18:31
Also possible duplicate of http://stackoverflow.com/questions/12302745/mysql-swap-primary-key-values — Edu, Jun 07 '16 at 18:35

score 1 · Answer 1 · answered Jun 07 '16 at 18:26

1

Simple workaround: use an intermediate value.

UPDATE Table 1 set ID=0 where text="Hello";
UPDATE Table 1 set ID=1 where text="Bye";
UPDATE Table 1 set ID=2 where text="Hello";

answered Jun 07 '16 at 18:26

Edu

2,354
5
32
36

score 1 · Answer 2 · answered Jun 07 '16 at 18:28

1

Try this :

UPDATE Table1 SET text="new value" where id=1

answered Jun 07 '16 at 18:28

TomiTeruz

13
1
10

Tin Tran · Accepted Answer · 2016-06-07T18:57:31.283

You can try swapping the text content of `Table 1` instead which would give the same effect as swapping their IDs. like below

START TRANSACTION;
SET @text1 := 'Hello', @text2 := 'Bye';
SELECT @id1 := ID FROM `Table 1` WHERE text=@text1;
SELECT @id2 := ID FROM `Table 1` WHERE text=@text2; 
UPDATE `Table 1` SET text=@text1 WHERE ID = @id2;
UPDATE `Table 1` SET text=@text2 WHERE ID = @id1;
COMMIT;

http://sqlfiddle.com/#!9/f287cd/1

If you have a unique constraint on text... you can use this

START TRANSACTION;
SET @text1 := 'Hello', @text2 := 'Bye';
SELECT @id1 := ID FROM `Table 1` WHERE text=@text1;
SELECT @id2 := ID FROM `Table 1` WHERE text=@text2; 
UPDATE `Table 1` SET text=NULL WHERE ID = @id2;
UPDATE `Table 1` SET text=@text2 WHERE ID = @id1;
UPDATE `Table 1` SET text=@text1 WHERE ID = @id2;
COMMIT;

http://sqlfiddle.com/#!9/a3d49/1

MySQL Update two rows in one atomic action

3 Answers3