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

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

CodePudding user response：

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

CodePudding user response：

If you are updating only one record in users table then you can use variables. Your query will be :

SET @ID = 0; 
SET @C = 0;
UPDATE 
   users a 
SET 
   a.number_of_items = @C := a.number_of_items - 1,
   current_group = @ID := current_group
WHERE 
   a.item_type = :item AND a.number_of_items > 0
;

UPDATE 
  group_user
SET
  item_count - 1
WHERE
  (@C = 0) AND (id = @ID)
;

I used two variables @ID, @C these variables will hold the values from users table then I used them in the second query. Note that the second query will be executed only when @C=0 .