Change value in a cell and update another cell in MySql table using Update query

I have MySQL table with with 5 columns. Product_id, product_name, pack_size , price and price change.

I have a requirement that if the value in price cell changes, it should update the cell value and save the updated value in price_change cell as well.

For example, in the dbfiddle attached, in the row 1, if price for eggs change from 1 to 3 , this means the new value in the price cell should be 3 and the price_chnage cell should be updated with old and new value 1,3.

Mysql Table Example

The output should look something like this :

Not sure if this is achievable but there is not harm in asking 🙂 Thank you Your help will be highly appreciated

Answer

I don’t suggest using your current schema, as storing the history of prices in a single data point, in CSV format, is not very usable or even scalable. Instead, I recommend creating an archive table into which you would insert a backup of the previous row, along with a timestamp. The insert operation would happen from a trigger after every update to your current main table.

DELIMITER //
CREATE TRIGGER trigger_after_update
AFTER UPDATE ON yourTable FOR EACH ROW
BEGIN

    INSERT INTO yourTableBackup (Product_id, Product_name, Pack_size, price, ts_of_backup)
    VALUES
        (NEW.Product_id, NEW.Product_name, NEW.Pack_size, NEW.price, NOW());

END;
//
DELIMITER ;

This assumes that you have defined a backup table by the name of yourTableBackup with the following table structure:

CREATE TABLE yourTableBackup (Product_id, Product_name, Pack_size, price, ts_of_backup);

With this backup table in place, you can fairly easily see the price history of a given product across time. Should you need to restore historical price values, or look for trends, you are now setup to do that.