updating data in a table from another table-CodePudding

I have two tables:
cart_item

id, session_id, product_id, quantity
1   1           2           5
2   1           3           5

product

id, quantity
2   50
3   75

How to correctly update the data in the product table based on the data in the cart_item table? I want to update the quantity of products in the table product.
By updating the data in product I will delete all products in the table cart_item. Here is what I would like to get when updating the data:
product

id, quantity
2   45
3   70

CodePudding user response：

You can try to use UPDATE ... JOIN to calculate the value.

UPDATE product p 
JOIN cart_item ci ON ci.product_id = p.id
SET p.quantity = p.quantity - ci.quantity

sqfliddle

CodePudding user response：

Maybe a query like below

update p
set p.quantity = p.quantity - sum(ci.quantity)
from product p
join cart_item ci
on ci.product_id=p.id
group by p.id