Update Case When, with multiple conditions
If you do not explicitly add an else
clause to a case
expression, it implicitly acts as though you've added else null
to it. So, your update statement is effectively equivalent to:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
ELSE NULL
END
Which explains why you see D
being "deleted".
One way around it is to explicitly add an else
clause that simply returns D
:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
ELSE D
END
Another way, which is a bit "clunkier" in syntax, but may perform slightly better, is to add a where
clause so only the relevant rows are updated:
UPDATE my_table
SET D = CASE
WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4'
WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9'
END
WHERE (A = 6 AND B = 1 AND C = 'red') OR (A = 8 AND B = 1 AND C = 'green')
DandyCC
Updated on June 05, 2022Comments
-
DandyCC almost 2 years
I have this table:
CREATE TABLE IF NOT EXISTS `my_table` ( `A` int(11) NOT NULL, `B` int(11) NOT NULL, `C` varchar(50) NOT NULL, `D` varchar(30) NOT NULL, PRIMARY KEY (`A`,`B`,`C`) )
I want to update several entries in just one query. I tried this:
UPDATE my_table SET D = CASE WHEN (A = 6 AND B = 1 AND C = 'red') THEN '1#2#3#5#4' WHEN (A = 8 AND B = 1 AND C = 'green') THEN '5#6#7#8#9' END
But this query updates all entries in the table. It updates perfectly the value of 'D' of the two entries I want to update, but it also deletes the values of "D" of the other entries, and I want them to stay with their previous values.
-
Rahul over 9 yearsTo me, the
WHERE
condition looks unnecessary. -
DandyCC over 9 yearsGreat! It works fine! Since I'm developing a php script, I'll definitely choose the first option :) Thanks!
-
rodrigo-silveira about 8 yearsThat where only looks unnecessary if you're updating a trivial toy table that doesn't have a whole lot of data in it. When your table has hundreds of thousands or millions of records, you definitely want to filter out the projection that you're updating.