Villermen Villermen - 3 months ago 10
MySQL Question

MySQL CASE to update multiple columns

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 a query like:

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


This is however not a valid query, is there anyway of achieving the same thing in valid SQL?

Gr.Viller

Answer
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
;