用于更新mysql数据库中所有列的脚本

Question

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?

Answer 1

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.