stack stack - 4 months ago 8
MySQL Question

How to update two different rows by one query?

Here is my table:

// mytable

+----+------+
| id | num |
+----+------+
| 1 | 400 |
| 2 | 200 |
| 3 | 200 |
| 4 | 250 |
+----+------+


And here is expected result:

+----+------+
| id | num |
+----+------+
| 1 | 390 |
| 2 | 200 |
| 3 | 220 |
| 4 | 250 |
+----+------+


As you see, I've subtracted
10
where
id = 1
, and I've added
20
where
id = 3


Here is my query:

UPDATE mytable SET num = num +/- (CASE WHEN num = 1 THEN 10 WHEN num = 3 THEN 20 END)
WHERE id IN (1,3)


How can I manage
+ or -
?

Answer

Just use a negative number in the case statement:

UPDATE mytable 
SET num = num + CASE WHEN id = 1 THEN -10 ELSE 20 END
WHERE id IN (1,3)