MySQL - How to update quantites in one table using values from multiple rows in another table

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".

Posted by:

Comments History


by: on
Makes sense, this question should be posted under MySQL Category as well.
 
by: on
SELECT ID, SUM(QTY) should be SELECT TABLE_1_ID AS ID, SUM(QTY)
 

Name :  

Email :  

Comment Below