Updating multiple columns depending on 1 CASE-condition

Question

I have a program that needs to synchronize it's frequently changing values (in temporary memory) with a database. The critical key (not primary!) in that table is the column id. My program changes the id but keeps the old id in memory, too.

Now, I would like to update several specified columns for multiple records/rows in one single statement. Furthermore, it should be reasonably fast for 5 up to 10 of such statements in 1 second with 4 GB RAM and ~ 50 MBit/s connection that is not only used for these sql-calls.

My sql-specifications

Server: 127.0.0.1 via
TCP/IP
Software: MySQL
Software version: 5.5.27 - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

I tried to use brackets...

UPDATE someTable
SET (id, name) = CASE id 
WHEN 1 THEN (111, "Dr. Frankenstein") 
WHEN 2 THEN (222, "the Monster") 
WHEN 3 THEN (333, "Mr. X") 
ELSE (id, name) END
WHERE id IN (1, 2, 3)

...which simply results in the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id, name) = CASE id WHEN 1 THEN (111, "Dr. Frankenstein") WHEN 2 THEN (222,' at line 2

Know I wonder: Is there a way to do it in just one statement with the current syntax? Would it be feasable that way or should I just split it into multiple statement which is ugly in terms of the program that makes the sql-calls.

Answers and suggestions are welcome!

Gordon Linoff · Accepted Answer · 2014-08-15 12:19:25Z

2

A case statement only returns one value:

UPDATE someTable
    SET id = (CASE id WHEN 1 THEN 111 WHEN 2 THEN 222 WHEN 3 THEN 333 ELSE id END),
        name = (CASE id WHEN 1 THEN 'Dr. Frankenstein'
                        WHEN 2 THEN 'the Monster'
                        WHEN 3 THEN 'Mr. X'
                        ELSE name
                END)
    WHERE id IN (1, 2, 3);

For performance, be sure you an an index on id. This will help with finding the records to update. Do note that changing the id value requires updating the index, which can be a bit longer than a normal update. However, expecting 5-10 transactions a second is reasonable.

edited Aug 15, 2014 at 12:19

answered Aug 15, 2014 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cHao Over a year ago

It's be pretty odd not to have id indexed; any self-respecting schema should already have set it as the primary key.

Florian R. Klein Over a year ago

I made that choice because there would be a very special id-conflict otherwise. Of course, I could change the whole design of the program and database to remediate this. For The moment, I like it quick and dirty.

Florian R. Klein Over a year ago

This solution does not work at this stage. At least my MySQL-Database disliked the semicolon after setting id. Exchaning it with a comma does the job. As in Sashi Kants answer the order is of great importance. Setting/Updating all columns before the id-column seems to be a must.

Gordon Linoff Over a year ago

@FlorianR.Klein . . . I don't know why there was a semicolon there. A semicolon should only be at the end of the statement. I mistyped what should have been a comma.

Sashi Kant · Accepted Answer · 2014-08-15 12:10:56Z

0

Hope this works:

UPDATE someTable
SET id = CASE 
id 
WHEN 1 THEN 111
WHEN 2 THEN 222
WHEN 3 THEN 333
ELSE id END
,
 name = CASE 
id 
WHEN 1 THEN "Dr. Frankenstein" 
WHEN 2 THEN "the Monster" 
WHEN 3 THEN "Mr. X" 
ELSE name END

WHERE id IN (1, 2, 3)

edited Aug 15, 2014 at 12:10

answered Aug 15, 2014 at 11:58

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

2 Comments

Florian R. Klein Over a year ago

This does not work because of the two redundant SET right before the name-assignment. There is problem in the order, too: I must switch the setting of id and name because the id is set immediately and the CASE of the name comes after the changes (find no ids because they have been altered).

Sashi Kant Over a year ago

Sorry, have changed that Kindly check

Collectives™ on Stack Overflow

Updating multiple columns depending on 1 CASE-condition

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related