Update multiple rows for 2 columns in MySQL

Question

Follow up to Update multiple rows in 1 column in MySQL.

What is the most efficient query for updating multiple rows in 2 columns?

UPDATE example_table SET variable1 = 12 WHERE id=1;
UPDATE example_table SET variable2 = 'blue' WHERE id=1;

UPDATE example_table SET variable1 = 42 WHERE id=2;
UPDATE example_table SET variable2 = 'red' WHERE id=2;

UPDATE example_table SET variable1 = 32 WHERE id=3;
UPDATE example_table SET variable2 = 'yellow' WHERE id=3;

Using case seems to be the most efficient, but how would I do it with multiple values being set at one time?

Answer 1

If you have a table named categories and say two columns are display_order and title Here is what you will do :

UPDATE categories
SET display_order = CASE id
    WHEN 1 THEN 32
    WHEN 2 THEN 33
    WHEN 3 THEN 34
END,
title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)

Answer 2

I think again CASE is the solution. The idea is to use separate CASE statements for each variable. Something like:

UPDATE `example_table`
SET `variable1` = (CASE
    WHEN `id` = 1 THEN 12
    WHEN `id` = 2 THEN 42
    WHEN `id` = 3 THEN 32
    END),
`variable2` = (CASE
    WHEN `id` = 1 THEN 'blue'
    WHEN `id` = 2 THEN 'red'
    WHEN `id` = 3 THEN 'yellow'
    END);

Hope it helps!

Answer 3

Some not need to put the this Sign on id ' ' eg: 'id' you can leave it blank...like

UPDATE example_table
SET variable1 = (CASE
    WHEN id = 1 THEN 12