Stack Stack - 5 months ago 10
MySQL Question

How to update a column for two separated ids?

I have these two queries:

$dbh
->prepare("UPDATE user
SET reputation = reputation - 15
WHERE id = ?")
->execute(array($old_author));

$dbh
->prepare("UPDATE user
SET reputation = reputation + 15
WHERE id = ?")
->execute(array($new_author));


I want to know can I do that by one single query? I mean can I both
- 15
and
+ 15
in
reputation
column for two users in the same query?

vkp vkp
Answer

Use a case expression to do it in one query.

UPDATE user
SET reputation = case id when '123' then reputation - 15
                         when '124' then reputation + 15  
                 end
where id in ('123','124')