Updating value, of a Column with PRIMARY KEY constraint

Question

I have a MySQL table with the following schema

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(7)      | NO   | UNI | NULL    | auto_increment |
| title | varchar(20) | NO   | PRI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

And here is the content in it.

+----+-------+
| id | title |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+

Question: I want to interchange the values in a single query so that the table now becomes

+----+-------+
| id | title |
+----+-------+
|  1 | b     |
|  2 | a     |
+----+-------+

I tried: UPDATE myTable SET title = CASE id WHEN 1 THEN "b" WHEN 2 THEN "a" END;

but it gives me an error ERROR 1062 (23000): Duplicate entry 'b' for key 'PRIMARY'

What should I do?

A solution found here in one of the links seems the only way as of now but I am still looking for a better solution

START TRANSACTION;
UPDATE prime SET title = CASE id WHEN 1 THEN "$b" WHEN 2 THEN "$a" END;
UPDATE prime SET title = CASE id WHEN 1 THEN SUBSTRING(title,2)  WHEN 2 THEN SUBSTRING(title,2) END;
COMMIT;

`id` should be primary key and `title` unique. You have it the other way around in your table definition. — juergen d, Nov 15 '13 at 16:55
The standard solution is to push 'b' to 'c', followed by 'a' to 'b', followed by 'c' to 'a'. Is that an option? — Strawberry, Nov 15 '13 at 17:15
possible duplicate of [how to swap values of two rows in mysql without violating unique constraint?](http://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint) (but it will be a bit more complicated to swap VARCHAR values than swapping INT values.) — ypercubeᵀᴹ, Nov 15 '13 at 17:56

ypercubeᵀᴹ · Accepted Answer · 2013-11-15T18:45:12.863

1

START TRANSACTION ;
    UPDATE prime SET title = 'zzzzz$$$$$xxxxx!@#$%' WHERE id = 1 ;

    UPDATE prime SET title = 'a' WHERE id = 2 ;

    UPDATE prime SET title = 'b' WHERE id = 1 ;
COMMIT ;

Comment, not related to the unique issue:

Use WHERE in your update statements, unless you want to update all the rows of the table. Your statement:

UPDATE myTable SET title = CASE id WHEN 1 THEN 'b' WHEN 2 THEN 'a' END;

(if it worked) it would also try to update all other rows (with id >= 3) with a NULL value because CASE has an implicit ELSE NULL part. Of course it would fail as the title is the primary key but in other cases, you would have undesirable effects.

edited Nov 15 '13 at 18:45

answered Nov 15 '13 at 18:39

ypercubeᵀᴹ

113,259
19
174
235

Yes I agree with your comment. I could add `ELSE title` there. – decached Nov 16 '13 at 09:06
That would work, too, yes. It would probably be less efficient though as it would check all other rows (that the new title is equal to the existing one.) It would also probably lock the whole table. – ypercubeᵀᴹ Nov 16 '13 at 09:30
I don't think it would check for all other rows. `ELSE` here probably works like `default` of `switch`. – decached Nov 16 '13 at 10:54
If you don't have a `WHERE` clause, then it means you want to update **all** rows of the table. – ypercubeᵀᴹ Nov 16 '13 at 11:01

dan-leech · Answer 2 · 2014-03-01T10:58:40.347

with PIMARY key help to use ORDER BY title

UPDATE myTable SET title = CASE id WHEN 1 THEN "a" WHEN 2 THEN "b" ElSE title END WHERE id in (1,2) ORDER BY id DESC;

if not work change direction to ASC or ORDER BY title. And don't fogot ELSE. it doesn't work wiout it.

I use it with id, like that:

UPDATE `table1` SET `id` = CASE `id` WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 0 THEN 1 ELSE `id` END WHERE `id` IN ( 1, 2, 0 ) ORDER BY `id` DESC

all works fine, but if update 0=>3,3=>2,2=>1 - 0 should update separatly.

Updating value, of a Column with PRIMARY KEY constraint

2 Answers2