stack stack - 5 months ago 25
MySQL Question

How to replace NULL with 0 in the query?

I have this query:

$db->prepare("UPDATE users
SET reputation = reputation +
(CASE WHEN id = ? THEN 2
WHEN id = ? AND NOT ? THEN 15 END)
WHERE id IN (?, ?); ")
->execute(array($author_ques_id, $author_ans_id, $isOwnAnswer,
$author_ans_id, $author_ques_id));


Please focus on this line:

WHEN id = ? AND NOT ? THEN 15 END)


When
NOT ?
is false (in other word
$isOwnAnswer = true
)
, the value of
reputatuin
for such a user will be
NULL
. Because
reputation = reputation + NULL
evaluates
reputation = NULL
.

Well how can I prevent of that? I want when that condition is false, then
reputation
doesn't change.

Answer

No need to wrap it with another CASE/COALESCE/IFNULL or anything, just use the CASE EXPRESSION ELSE , in case all the conditions are not met, the ELSE will be used.

UPDATE users 
           SET reputation = reputation +
                            CASE WHEN id = ? THEN 2
                                 WHEN id = ? AND NOT ? THEN 15
                                 ELSE 0
                            END
          WHERE id IN (?, ?);
Comments