CODEWITHSUNDEEP
phpmysqlsql
stack
stack

Reputation: 10218

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.

Upvotes: 1

Views: 201

Answers (2)

sagi
sagi

Reputation: 40481

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 (?, ?);

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Use COALESCE:

UPDATE users 
SET reputation = reputation +
                 COALESCE((CASE 
                             WHEN id = ? THEN 2
                             WHEN id = ? AND NOT ? THEN 15 
                           END), 0)

Upvotes: 2

Related Questions