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
WHE开发者_运维知识库N 1 THEN 'New Title 1'
WHEN THEN
WHEN THEN
END
WHERE id IN (1,2,3)
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)
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
精彩评论