Update multiple rows with different values in a single query - MySQL

Question

I'm new to MySQL.

I'm using this to update multiple rows with different values, in a single query:

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    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)

I am using "WHERE" to improve performance (without it every row in the table would be tested).

But what if I have this senario (when I don't want to update title for id 2 and 3):

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
    END
WHERE id IN (1,2,3)

The above code will change the title for id 2 and 3 into "NULL"...

What is the right way to make the query, but skip updating title for id 2 and 3 and still keep the performance "WHERE id IN" gives ?

Maybe like this

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN  THEN 
        WHEN  THEN 
    END
WHERE id IN (1,2,3)

Joe Stefanelli · Accepted Answer · 2011-10-13 19:45:13Z

12

Set title equal to itself when you don't want to update it to a different value.

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        ELSE title
    END
WHERE id IN (1,2,3)

answered Oct 13, 2011 at 19:45

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

Comments

Mohsen Beiranvand · Accepted Answer · 2012-08-27 06:59:22Z

0

UPDATE `table_name` SET `field_name1` = CASE `id`
WHEN '1' THEN 'value_1'
WHEN '2' THEN 'value_2'
WHEN '3' THEN 'value_3'
ELSE `field_name1`
END,
`field_name2`= CASE id
WHEN '1' THEN 'value_1'
WHEN '2' THEN 'value_2'
WHEN '3' THEN 'value_3'
ELSE `field_name2`
END

answered Aug 27, 2012 at 6:59

Mohsen Beiranvand

1,02210 silver badges14 bronze badges

Collectives™ on Stack Overflow

Update multiple rows with different values in a single query - MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related