CODEWITHSUNDEEP
sqlsql-server
Manu'sa Bora
Manu'sa Bora

Reputation: 131

Calculate the 2 columns from table and insert the result as other column in the same table

I have created a table with some columns like orderID,custId,unitprice,Quantity.Now I want to add the other column as Totalprice by multiplying quantity*unitprice.

I have altered the table by adding the totalprice column to the existing table alter table orders add totalprice int
and then I have tried with insert query
insert into orders (totalprice) select quantity*unitprice from orders
And also I have tried with creating some temp table also but the temp table would not used furtherly.

Please let me know how to put this query in order to insert a column.

Totalprice=quantity*unitprice

Upvotes: 1

Views: 507

Answers (2)

Jonathan M
Jonathan M

Reputation: 1889

You do not have to INSERT any record once the column is added. What you need is to UPDATE the records.

UPDATE Orders SET TotalPrice = Quantity * UnitPrice

Alternatively, you could take a look at computed columns. This would save you the trouble of making sure your Total column is up to date with both the Quantity and UnitPrice columns.

ALTER TABLE Orders ADD TotalPrice AS Quantity * UnitPrice

Upvotes: 2

M.Ali
M.Ali

Reputation: 69554

You can make this total price column a computed column. as follows ....

ALTER TABLE dbo.TableName 
ADD  totalprice AS (quantity*unitprice)
GO

Upvotes: 2

Related Questions