Column calculated from another column

Question

Given the following table   id   value -------------- 1     6 2     70   Is there a way to add a column that is automatically calculated based on another column in the same table  Like  a VIEW  but part of the same table  As an example  calculated would be half of value  Calculated should be automatically updated when value changes  just like a VIEW would be   The result would be   id   value   calculated ----------------------- 1     6       3 2     70      35

User · Answer

If you want to add a column to your table which is automatically updated to half of some other column, you can do that with a trigger.

But I think the already proposed answer are a better way to do this.

Dry coded trigger :

CREATE TRIGGER halfcolumn_insert AFTER INSERT ON table
  FOR EACH ROW BEGIN
    UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
  END;
CREATE TRIGGER halfcolumn_update AFTER UPDATE ON table
  FOR EACH ROW BEGIN
    UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
  END;

I don't think you can make only one trigger, since the event we must respond to are different.

User · Answer

You can use generated columns from MYSQL 5 7   Example Usage   ALTER TABLE tbl test ADD COLUMN calc val INT  GENERATED ALWAYS AS     column1  - 1    16     column2   STORED    VIRTUAL   STORED    Virtual  calculated on the fly when a record is read from a table  default  Stored   calculated when a new record is inserted updated within the table

User · Answer

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

User · Answer

I hope this still helps someone as many people might get to this article  If you need a computed column  why not just expose your desired columns in a view   Don t just save data or overload the performance with triggers    simply expose the data you need already formatted calculated in a view   Hope this helps

User · Answer

MySQL 5 7 supports computed columns  They call it  Generated Columns  and the syntax is a little weird  but it supports the same options I see in other databases   https   dev mysql com doc refman 5 7 en create-table html create-table-generated-columns

User · Answer

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

User · Answer

krtek s answer is in the right direction  but has a couple of issues   The bad news is that using UPDATE in a trigger on the same table won t work  The good news is that it s not necessary  there is a NEW object that you can operate on before the table is even touched   The trigger becomes   CREATE TRIGGER halfcolumn update BEFORE UPDATE ON my table   FOR EACH ROW BEGIN     SET NEW calculated   NEW value 2    END    Note also that the BEGIN   END  syntax has to be parsed with a different delimiter in effect  The whole shebang becomes   DELIMITER    CREATE TRIGGER halfcolumn insert BEFORE INSERT ON my table   FOR EACH ROW BEGIN     SET NEW calculated   NEW value 2    END     CREATE TRIGGER halfcolumn update BEFORE UPDATE ON my table   FOR EACH ROW BEGIN     SET NEW calculated   NEW value 2    END     DELIMITER

[mysql] Column calculated from another column?

Examples related to mysql

Examples related to sql