How to automatically update a MYSQL column value using formula and data from other columns in same row?

Question

Question on mySQL.

I have a table with columns like below:

BasePrice, Discount, NetPrice

Now assume that the calculation of net price happens like below:

NetPrice = BasePrice - Discount

Now, I do not mind entering the BasePrice and Discount values into the column on my own, manually using phpMyadmin (that is, direct backend updation). Since the NetPrice value involves calculation as shown above, I do not want to update the column value for each row in the table, but would prefer the database updating it automatically for me ( not that I am weak at math :) ).

Is there someway to have the database update the NetPrice column value automatically? I know I can use php to get the other two column values, calculate and then update the NetPrice value, again using php code, but would prefer the db doing it on its own without any php/server side scripting.

I use mySQL and phpMyadmin for dev.

Thanks.

Answer 1

You could use a single query to update all table:

UPDATE your_table
SET NetPrice = BasePrice - Discount

or you can use a trigger to alter that column anytime a row is added.
Something like this:

CREATE TRIGGER onInsert BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    SET NEW.NetPrice = NEW.BasePrice - NEW.Discount;
END;

Answer 2

As an alternative to Marco's (+1) answer, for just an INSERT, the simplest approach might be to reference columns assigned previously in the statement. For example:

INSERT INTO t (BasePrice, Discount, NetPrice) VALUES (17.00, 2.50, BasePrice-Discount);

For that NetPrice column to calculated correctly, the BasePrice and Discount columns must appear in the insert before the expression that calculates NetPrice. (The values assigned to each column is available later in the statement, after the column is assigned a value.)

If you always want to guarantee that NetPrice is kept "in sync" if either BasePrice or Discount is modified, then a BEFORE UPDATE trigger will do that for you, so the combination of a BEFORE INSERT and BEFORE UPDATE trigger would be appropriate. (See the answer from Marco.)