CODEWITHSUNDEEP
mysql
decached
decached

Reputation: 915

Updating value, of a Column with PRIMARY KEY constraint

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;

Upvotes: 1

Views: 3104

Answers (2)

dan-leech
dan-leech

Reputation: 21

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.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

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.

Upvotes: 1

Related Questions