I have two tables cart and cages, I am trying to update the 'remaining' column by checking the item_name for all entries in cart and subtracting the quantity in the corresponding tables.
[cages]
id name total remaining
-----------------------------------
1 Cage1 10 10
2 Cage2 15 15
3 Cage3 10 10
[cart]
id item_name quantity
-----------------------------------
1 Cage1 2
2 Book4 3
There are several tables in the database that would need to be checked upon purchase however, I am not sure what would be the best way of performing these queries without joining all tables. This is the query I have been attempting to use.
UPDATE cages
SET remaining = (remaining - cart.quantity
WHERE cart.item_name = cages.name);