Column calculated from another column?

by

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.

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. Get records with max value for each group of grouped SQL results
  3. How to populate a table with a range of dates?
  4. MYSQL OR vs IN performance
  5. SELECTING with multiple WHERE conditions on same column
  6. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  7. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  8. Cannot delete or update a parent row: a foreign key constraint fails
  9. Using union and order by clause in mysql
  10. Using an Alias in SQL Calculations
  11. Can MySQL replace multiple characters?
  12. OUTPUT Clause in MySQL
  13. Using ORDER BY and GROUP BY together
  14. Sorting by date & time in descending order?
  15. Case statement in MySQL
  16. MySQL Pivot Table Column Data as Rows
  17. updating columns with a sequence number mysql
  18. MySQL, Concatenate two columns
  19. Select a column with a keyword name
  20. MySQL: Inner join vs Where [duplicate]
  21. ERROR 1148: The used command is not allowed with this MySQL version
  22. Selecting from two tables with inner join and limit [duplicate]
  23. Use Composite Primary Key as Foreign Key
  24. How to select data where a field has a min value in MySQL?
  25. How to SELECT by MAX(date)?
  26. get a recursive parent list
  27. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  28. What happens when I exhaust a bigint generated key? How to handle it?
  29. MySQL update statement to store ranking positions
  30. How to convert a varchar column type to date type without losing the dates

Leave a Comment