MySQL CASE to update multiple columns

Question

I would like to update multiple columns in my table using a case statement, but I cannot find how to do this (is this even possible). I came up with the following invalid reference query:

UPDATE tablename SET
    CASE name
        WHEN 'name1' THEN col1=5,col2=''
        WHEN 'name2' THEN col1=3,col2='whatever'
        ELSE col1=0,col2=''
    END;

Is there any way of achieving the expected result with valid SQL?

Possible duplicate: http://stackoverflow.com/questions/8358642/case-statement-in-sql-how-to-return-multiple-variables — ean5533, Dec 02 '12 at 21:20
Again, not really. This is an update statement, not an insert one. — Villermen, Jan 12 '17 at 13:28
You asked if there was *anyway of achieving the same thing in valid SQL*. The answer is yes, using an insert statement. Note that the linked question is also asking how to combine updates. — shmosel, Jan 12 '17 at 20:08
It is not the same thing if it inserts records instead of only updating existing records. The accepted answer to the linked question is an insertion whereas I was explicitly looking for an UPDATE only answer. The linked question indeed also wants an UPDATE answer, but has an INSERT answer accepted. I don't know why this happened, but it would not answer this question. — Villermen, Jan 13 '17 at 10:22
Because it doesn't insert records if there are duplicate values for a unique key. I take it there's no unique key on `name`? — shmosel, Jan 18 '17 at 02:09

score 45 · Accepted Answer · answered Dec 02 '12 at 21:16

45

UPDATE tablename
SET col1 = CASE WHEN name = 'name1' THEN 5 
                WHEN name = 'name2' THEN 3 
                ELSE 0 
           END
 , col2 = CASE WHEN name = 'name1' THEN '' 
               WHEN name = 'name2' THEN 'whatever' 
               ELSE '' 
          END
;

answered Dec 02 '12 at 21:16

bobwienholt

17,420
3
40
48

Thanks for this! I'm going to use ean5533's format since it includes the CASE name, but this one is better formatted so I marked it as the anser. – Villermen Dec 02 '12 at 21:29
1

Interesting... I didn't know that was valid syntax. Learn something new everyday. – bobwienholt Dec 02 '12 at 21:31

score 9 · Answer 2 · answered Dec 02 '12 at 21:16

9

I don't know of any clean way to do what you're asking. An equivalent valid SQL update would be:

UPDATE tablename SET
    col1 = CASE name WHEN 'name1' THEN 5 WHEN 'name2' THEN 3 ELSE 0 END,
    col2 = CASE name WHEN 'name1' THEN '' WHEN 'name2' THEN 'whatever' ELSE '' END;

Of course this isn't pretty and requires repeating the same cases (e.g. 'name1') multiple times, but I just don't think it's possible any other way.

answered Dec 02 '12 at 21:16

ean5533

8,884
3
40
64

This does the trick, and the repeating won't be that hard for PHP to handle =) Thanks! – Villermen Dec 02 '12 at 21:25

score 2 · Answer 3 · answered Aug 25 '16 at 23:29

If name has a unique index and your values are known to exist in the table, you can use this trick:

INSERT INTO tablename (name, col1, col2)
VALUES ('name1', 5, '')
     , ('name2', 3, 'whatever')
ON DUPLICATE KEY UPDATE
       col1 = VALUES(col1)
     , col2 = VALUES(col2);

If there are any additional NOT NULL columns without a default value, you'll have to add dummy values for those. Just leave them out of the ON DUPLICATE KEY UPDATE and they'll be ignored.

MySQL CASE to update multiple columns

3 Answers3

Linked