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?

HalfWebDev · Accepted Answer · 2012-02-19 18:41:05Z

11

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)

edited Feb 19, 2012 at 18:41

answered Feb 19, 2012 at 18:13

HalfWebDev

7,74814 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhay · Accepted Answer · 2012-02-19 18:17:00Z

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!

answered Feb 19, 2012 at 18:17

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Comments

Druid · Accepted Answer · 2012-08-01 07:08:56Z

2

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

edited Aug 1, 2012 at 7:08

Druid

6,4634 gold badges44 silver badges57 bronze badges

answered Aug 1, 2012 at 2:38

Vadz

291 gold badge1 silver badge9 bronze badges

Comments

aleroot · Accepted Answer · 2012-02-19 17:33:34Z

0

Something like this could be what you want to do :

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

answered Feb 19, 2012 at 17:33

aleroot

73k32 gold badges182 silver badges220 bronze badges

1 Comment

Don P Over a year ago

Thanks Aleroot, but your answer doesn't deal with updating multiple rows, just multiple columns.

Collectives™ on Stack Overflow

Update multiple rows for 2 columns in MySQL

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related