Geek Answers Handbook

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 multiple records in only 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 records in the table. It completely updates the “D” value of the two records that I want to update, but also deletes the “D” values ​​of the other records, and I want them to stay with their previous values.

+4
source share
1 answer

Unless you explicitly add a sentence elseto an expression case, it implicitly acts as if you added to it else null. So your update statement is effectively equivalent:

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

, D "".

: else, 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

, "clunkier" , , , where, :

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

More articles:


All Articles