stack stack - 5 months ago 6
SQL Question

How can I write multiple conditions in CASE statement?

Here is my query:

UPDATE mytable
SET mycol = mycol +
(CASE id
WHEN ? THEN 5
WHEN ? THEN 10
WHEN ? THEN 15
WHEN ? THEN 20
END)
WHERE id IN (?, ?, ?, ?)


Well I need to investigate one more condition for all of those conditions. So I write my query like this:

UPDATE mytable
SET mycol = mycol +
(CASE
WHEN id = ? AND $bool THEN 5
WHEN id = ? AND $bool THEN 10
WHEN id = ? AND $bool THEN 15
WHEN id = ? AND $bool THEN 20
END)
WHERE id IN (?, ?, ?, ?)


This ^ query works. But as you see I've wrote a constant condition (
$bool
- this is a PHP variable which is containing
1
or
0
)
several times. Now I'm looking for a MySQL syntax to lets me I write
$bool
once for all cases. Is that possible?

Answer

You can use IF:

UPDATE mytable
SET mycol = mycol + IF($bool, (CASE
                                  WHEN id = ? THEN 5
                                  WHEN id = ? THEN 10
                                  WHEN id = ? THEN 15
                                  WHEN id = ? THEN 20
                               END), 0)
WHERE id IN (?, ?, ?, ?)

If $bool is true then the value returned by the CASE expression will be added to mycol, otherwise 0 is added.

Comments