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?

Answer 1

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 ; 

Answer 2

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.