How to update second table based off return value from first table

Question

Here is the issue to solve. I have a table USERS and a table GROUP_USER. I am updating table USERS and the data I update in that table will directly affect the GROUP_USER table. Here are the tables

**Users table**
id
number_of_items
group_type       //**group_type** value from GROUP_USER table
current_group    //id column value from GROUP_USER table

**Group_user table**
id
group_type
item_count


//Query to update users table
"UPDATE users SET number_of_items = number_of_items - 1 WHERE item_type = :item AND number_of_items > 0"

What I want is...IF after subtracting (1) item from number_of_items column in USERS table, the remainder equals zero(0), then subtract a value of (1) from the 'item_count' column in the GROUP_USER table WHERE group_type in GROUP_USER table is equal to current_group in USERS table. So that for every number_of_items that reaches zero, it will subtract 1 from the Group_user table based off of which group_type they are a part of.

a query similar to this, if even possible:

UPDATE 
   users a 
SET 
   a.number_of_items = a.number_of_items - 1 
WHERE 
   a.item_type = :item AND a.number_of_items > 0

 (
    if a.number_of_items - 1 = 0
    UPDATE 
       group_user b, users a 
    SET
       b.item_count - 1
    WHERE
       b.id = a.current_group
  )

I'll probably have to run the next query separate, but it will update all users number_of_items to 0 if current_group is 0. Sorry, its a bit complicated.

UPDATE
    users
SET
    current_group = 0
WHERE
    number_of_items = 0

Answer 1

Use a LEFT join of the tables in the UPDATE statement:

UPDATE users u
LEFT JOIN group_user g 
ON g.id = u.current_group AND u.number_of_items = 1
SET u.number_of_items = u.number_of_items - 1,
    g.item_count = g.item_count - 1    
WHERE u.item_type = :item AND u.number_of_items > 0;

Maybe the conditions in the ON clause of the join need to include the columns group_type also:

ON g.id = u.current_group AND g.group_type = u.group_type AND u.number_of_items = 1