CODEWITHSUNDEEP
phpmysqlsqlcase
stack
stack

Reputation: 10228

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?

Upvotes: 0

Views: 93

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 3

Related Questions