MySQL CASE to update multiple columns

mysql sql case
Villermen picture Villermen · Dec 2, 2012 · Viewed 33k times · Source

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

bobwienholt picture bobwienholt · Dec 2, 2012 
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
;

Related questions

Case statement in MySQL

I have a database table called 'tbl_transaction' with the following definition: id INT(11) Primary Key action_type ENUM('Expense', 'Income') action_heading VARCHAR (255) action_amount FLOAT I would like to generate two columns: Income Amt and Expense Amt. Is …

Read More
How do I use properly CASE..WHEN in MySQL

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3: SELECT CASE course_enrollment_settings.base_price WHEN course_enrollment_settings.base_price = 0 THEN 1 WHEN course_enrollment_settings.…

Read More
Mysql SELECT CASE WHEN something then return field

I have two field nnmu and nnmi , if nnmu is equal to 1, I need to return naziv_mesta from **mesto_istovara**, else if it's =0 I need to return naziv_mesta from mesto_utovara table and reverse, if nnmi is equal …

Read More