CODEWITHSUNDEEP
mysqlsql
Ellie Boock
Ellie Boock

Reputation: 1

MySQL Help: How do I automatically update a column?

I have a table called cups with the columns item_num, product_name, amount, price, total.

I want to automatically update total = amount * price when I enter a new row with just the item_num, product_name, amount, price.

How do I do this?

Upvotes: 0

Views: 47

Answers (2)

OO7
OO7

Reputation: 690

Don't worry, you can create a simple query that produce really up-to-date rows result contains common calculation in "total" colum which complemented by sql standard (ISO/IEC). Take a look this one..

select
  item_num,
  product_name,
  amount,
  price,
  (amount * price) as total
from cups

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use a generated column:

alter table cups add column total decimal(20, 4)
    generated always as (amount * price);

This calculates the total when the column is used, so it is always up-to-date.

Upvotes: 3

Related Questions