DandyCC DandyCC - 7 months ago 24
SQL Question

Update Case When, with multiple conditions

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.

Answer

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')
Comments