Update from INNER JOIN specific ids but all were updated

Question

Currently, I am trying to implement a restaurant application. In DB I have category table and item table, one category must have many items and one item must have one category so this is a one to many relationships, I want to update some category records when a category hasn't any items that mean item if qty on hand = 0 category status should be updated as "deactivated" these are my tables

+-------+----------+-------------------+
| 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 |
+--------+-------+--------+-----------+-----------------+------------+----------+

this is the query that I tried so far.

UPDATE catagory  INNER JOIN item  ON catagory.CatId = item.CatId SET catagory.`status` = "deactivated"
WHERE ((SELECT SUM(item.QtyOnHand)) >= 0);

this query updates all category CatId that have in the item table as a beginner, for MySQL, I want some help.

Answer 1

simply add where clause inside your subquery and filter them as you want. like this.

UPDATE catagory INNER JOIN item ON catagory.CatId  = item.CatId 
SET catagory.status ="deactivated"
WHERE (SELECT SUM(item.QtyOnHand) where catagory.CatId  = item.CatId GROUP By item.CatId) <= 0 ;