Column calculated from another column?

Solution 1:

Generated Column is one of the good approach for MySql version which is 5.7.6 and above.

There are two kinds of Generated Columns:

• Virtual (default) - column will be calculated on the fly when a record is read from a table
• Stored - column will be calculated when a new record is written/updated in the table

Both types can have NOT NULL restrictions, but only a stored Generated Column can be a part of an index.

For current case, we are going to use stored generated column. To implement I have considered that both of the values required for calculation are present in table

CREATE TABLE order_details (price DOUBLE, quantity INT, amount DOUBLE AS (price * quantity));

INSERT INTO order_details (price, quantity) VALUES(100,1),(300,4),(60,8);

amount will automatically pop up in table and you can access it directly, also please note that whenever you will update any of the columns, amount will also get updated.

Solution 2:

If it is a selection, you can do it as:

SELECT id, value, (value/2) AS calculated FROM mytable

Else, you can also first alter the table to add the missing column and then do an UPDATE query to compute the values for the new column as:

UPDATE mytable SET calculated = value/2;

If it must be automatic, and your MySQL version allows it, you can try with triggers