|
Here is a MySQL query that will enable you to update table one's values using multiple values from multiple rows using table two's values:
UPDATE TABLE_1 AS T1, (SELECT ID, SUM(QTY) AS qty FROM TABLE_2 WHERE TABLE_1_ID IN (NULL, 'ID_1', 'ID_2', 'ID_3') GROUP BY ID) AS T2 SET T1.QTY = T1.QTY - qty WHERE T2.ID = T1.ID
TABLE_1
| | |
ID |
| |
QTY |
| |
| | |
1 |
| |
20 |
| |
| | |
2 |
| |
19 |
| |
| | |
3 |
| |
24 |
| |
| | |
4 |
| |
5 |
| |
TABLE_2
| | |
ID |
| |
TABLE_1_ID |
| |
QTY |
| |
| | |
1 |
| |
1 |
| |
2 |
| |
| | |
2 |
| |
3 |
| |
1 |
| |
| | |
3 |
| |
3 |
| |
2 |
| |
| | |
4 |
| |
2 |
| |
1 |
| |
| | |
5 |
| |
1 |
| |
3 |
| |
| | |
6 |
| |
2 |
| |
1 |
| |
| | |
7 |
| |
4 |
| |
3 |
| |
| | |
8 |
| |
4 |
| |
1 |
| |
| | |
9 |
| |
2 |
| |
1 |
| |
| | |
10 |
| |
4 |
| |
1 |
| |
If the above information is in the database, then TABLE_1 ID 1 would be reduced to 15, TABLE_1 ID 2 would be reduced to 16, TABLE_1 ID 3 would be reduced to 21 and TABLE_1 ID 4 would be reduced to 0.
In order to add to the values in TABLE_1 using values from TABLE_2, just change "T1.QTY - qty" to "T1.QTY + qty".
|