Villermen Villermen -4 years ago 176
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=''

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


Answer Source
UPDATE tablename
SET col1 = CASE WHEN name = 'name1' THEN 5 
                WHEN name = 'name2' THEN 3 
                ELSE 0 
 , col2 = CASE WHEN name = 'name1' THEN '' 
               WHEN name = 'name2' THEN 'whatever' 
               ELSE '' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download