mysql - deduct values of a column in a table from another table

Question

i want to deduct the amount of ingredients of a product every time it is ordered

so I have 2 tables, PRODUCT_INGREDIENTS and INVENTORY I want to deduct the amount of ingredients of a product(PRODUCT_INGREDIENTS), every time it is ordered, in the INVENTORY table

BEFORE ORDERING:

PRODUCT_INGREDIENT
+--------+-----------------+--------+
|item_id | ingredient_name | amount |
+--------+-----------------+--------+
|   2    |      sugar      |  10    |
+--------+-----------------+--------+
|   2    |      milk       |  15    |
+--------+-----------------+--------+

INVENTORY
+--------+-----------------+--------+
|  id    | ingredient_name | amount |
+--------+-----------------+--------+
|   1    |      sugar      |  100   |
+--------+-----------------+--------+
|   2    |      milk       |  100   |
+--------+-----------------+--------+

AFTER ORDERING

INVENTORY
+--------+-----------------+--------+
|  id    | ingredient_name | amount |
+--------+-----------------+--------+
|   1    |      sugar      |   90   |
+--------+-----------------+--------+
|   2    |      milk       |   85   |
+--------+-----------------+--------+

i want my table to look like that after transaction. how can I do it in MY SQL. I'm in desperate need of codes

标签： mysql

Answer 1

我用这段代码解决了

 UPDATE INVENTORY i, PRODUCT_INGREDIENT p SET i.amount = i.amount - p.amount WHERE i.ingredient_name = p.ingredient_name

我从先生那里得到了这个想法。马里奥回答了这个问题。谢谢你。