Hi I am using PHP to manipulate information in my MySQL database. However I am looking for a way to update a table (all records need to be updated) based on information within another table.
For example I have a list of products lets say 10 each with a unique id stored in a products table. I have a purchases table which has the same product ID and the amount of purchases done for each product. I want to update each product in the products table to reflect the total purchases made for each product and store it in a column called instock which is part of the products table.
How can this be done?
If I understand your situation correctly, you're dealing with a stock-count. When an item is purchased (represented by a entry in the Products
table) then the stock count figure should be decreased. This should happen within the same transaction as the new entry to the Products
table to keep your data consistent. I would recommend using a Trigger on the table to implement this. You'll find lots of information about implementing triggers in MySQL on this site. A trigger you could use might look something like this:
CREATE TRIGGER update_stock_count
BEFORE INSERT ON Purchases
FOR EACH ROW
BEGIN
UPDATE Products SET stock_count = stock_count - NEW.quantity_ordered
WHERE product_id = NEW.product_id;
END;
This trigger doesn't take into account that there might not be enough stock of a product, nor does it handle updates or deletes on the Purchases
table but it could be modified to do so.