目前，我正在尝试实现一个餐厅应用程序。在数据库中我有category表和item表，一个类别必须有多个项目，一个项目必须有一个类别，所以这是一个一对多的关系，当一个类别没有任何项目表示项目时，我想要更新一些类别记录如果qty on hand = 0类别status应该更新为"deactivated"这些是我的表
+-------+----------+-------------------+
| CatId | status | catagory |
+-------+----------+-------------------+
| C001 | Deactive | SeaFood |
| C002 | Deactive | ITALIAN & western |
| C003 | Active | Kottu |
| C004 | Active | Rice |
+-------+----------+-------------------+
+--------+-------+--------+-----------+-----------------+------------+----------+
| ItemId | CatId | Price | QtyOnHand | iteamName | Date | Time |
+--------+-------+--------+-----------+-----------------+------------+----------+
| I001 | C003 | 650.00 | 30 | chease kottu | 2020-04-26 | 19:55:59 |
| I002 | C003 | 650.00 | 25 | vgetable kottu | 2020-04-26 | 19:55:59 |
| I003 | C003 | 450.00 | 3 | chicken koththu | 2020-04-27 | 08:32:12 |
+--------+-------+--------+-----------+-----------------+------------+----------+这是我到目前为止尝试过的查询。
UPDATE catagory INNER JOIN item ON catagory.CatId = item.CatId SET catagory.`status` = "deactivated"
WHERE ((SELECT SUM(item.QtyOnHand)) >= 0);此查询将更新项目表中作为初学者的所有类别CatId，对于MySQL，我需要一些帮助。
转载请注明出处:http://www.sthongjia.com/article/20230526/2328199.html