mysql - 如何通过一个查询更新多行？

Question

我有这张表:

// user
+----+----------+---------------------+-------------+
| id |   name   |       email         |  reputation |
+----+----------+---------------------+-------------+
| 1  | Jack     | jack12@google.com   | 10          |
| 2  | John     | J_1343@ymail.com    | 1423        |
| 3  | Ali      | 1992Ali@yahoo.com   | 322         |
| 4  | Peter    | p.54@gmail.com      | 950         |
+----+----------+---------------------+-------------+

我还有两个身份证号码。 $op 和 $user。我想用 2(对于 $op)和 15(对于 $user)加上当前的声誉。所以这是预期的输出:

$op = 3;
$user = 1;

// user
+----+----------+---------------------+-------------+
| id |   name   |       email         |  reputation |
+----+----------+---------------------+-------------+
| 1  | Jack     | jack12@google.com   | 25          |
| 2  | John     | J_1343@ymail.com    | 1423        |
| 3  | Ali      | 1992Ali@yahoo.com   | 324         |
| 4  | Peter    | p.54@gmail.com      | 950         |
+----+----------+---------------------+-------------+

我怎样才能通过一个查询做到这一点？

---

我可以通过两个单独的查询来做到这一点:

UPDATE user SET reputation = reputation + 2 WHERE id = :op
UPDATE user SET reputation = reputation + 15 WHERE id = :user

Answer 1

为什么不试试:

UPDATE user SET reputation = reputation + (CASE id WHEN :op THEN 2 WHEN :user THEN 15 ELSE 0 END) WHERE user in (:user, :op)