mysql - 从另一个表中减去一个表中的列的值

Question

我想在每次订购时扣除产品的成分数量

所以我有 2 个表，Product_INGREDIENTS 和 INVENTORY我想在每次订购时在 INVENTORY 表中扣除产品的成分数量 (PRODUCT_INGREDIENTS)

订购前:

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

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

订购后

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

我希望我的 table 在交易后看起来像这样。我怎样才能在 MY SQL 中做到这一点。我迫切需要代码

Answer 1

您可以使用以下代码根据 PRODUCT_INGREDIENT 更新您的 INVENTORY 表:

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