CODEWITHSUNDEEP
mysqlsql
Alissa L.
Alissa L.

Reputation: 103

How to add a column to an SQL table that is a multiplication of two other columns?

I have a table called SalesOrderDetail and I need to append a column called "Revenue" which is the multiplication of two other columns: UnitPrice and OrderQty.

I am using the following code:

# creating columns revenue
ALTER TABLE SalesOrderDetail
ADD revenue DOUBLE NOT NULL;

# insert the revenues into the revenue column
INSERT INTO SalesOrderDetail(revenue)
SELECT UnitPrice*OrderQty
FROM SalesOrderDetail;

but I am getting the error: Error Code: 1048. Column 'revenue' cannot be null

Why is column revenue turning out to be null? I tried multiplicating both columns before and got the expected results, so it must be something in the code. Thank you!

Upvotes: 0

Views: 1537

Answers (2)

jarlh
jarlh

Reputation: 44786

Avoid data inconsistency, use a generated column:

ALTER TABLE SalesOrderDetail
ADD revenue NUMERIC(20, 4) 
GENERATED ALWAYS AS (UnitPrice*OrderQty) STORED;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Add the column as a computed column:

ALTER TABLE SalesOrderDetail
    ADD revenue NUMERIC(20, 4) GENERATED ALWAYS AS (UnitPrice*OrderQty) VIRTUAL;

It is then calculated when the value is used in a query.

Note that this uses a NUMERIC type instead of a floating point number. Always use NUMERIC/DECIMAL for monetary amounts.

Upvotes: 5

Related Questions