CODEWITHSUNDEEP
mysqlsqlsql-update
stack
stack

Reputation: 10228

How to update multiple rows by one query?

I have this table:

// user
+----+----------+---------------------+-------------+
| id |   name   |       email         |  reputation |
+----+----------+---------------------+-------------+
| 1  | Jack     | [email protected]   | 10          |
| 2  | John     | [email protected]    | 1423        |
| 3  | Ali      | [email protected]   | 322         |
| 4  | Peter    | [email protected]      | 950         |
+----+----------+---------------------+-------------+

Also I have two id number. $op and $user. I want to plus current reputation with 2 (for $op) and 15 (for $user). So this is expected output:

$op = 3;
$user = 1;

// user
+----+----------+---------------------+-------------+
| id |   name   |       email         |  reputation |
+----+----------+---------------------+-------------+
| 1  | Jack     | [email protected]   | 25          |
| 2  | John     | [email protected]    | 1423        |
| 3  | Ali      | [email protected]   | 324         |
| 4  | Peter    | [email protected]      | 950         |
+----+----------+---------------------+-------------+

How can I do that by one query?

I can do that by two separated queries:

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

Upvotes: 2

Views: 80

Answers (2)

David
David

Reputation: 3773

Why not try:

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

Upvotes: 3

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3128

Try this

UPDATE user SET reputation = (CASE WHEN id = :op THEN reputation + 2 
                                  WHEN id = :user THEN reputation + 15
                                  END)

Upvotes: 0

Related Questions