Multiplying two columns of same table and storing

Question

I m having a table

SALES(sno,comp_name,quantity,costperunit,totalcost)

After supplying the costperunit values, totalcost need to be calculated as "totalcost=quantity*costperunit".

I want to multiply 'quantity' and 'costperunit' columns and store the result in 'totalcost' column of same table.

I have tried this:

insert into SALES(totalcost) select quantity*costperunit as res from SALES

But It failed!

Somebody please help me in achieving this.. Thanks in Advance

Answer 1

try this while inserting new row

      INSERT INTO test(sno,comp_name,quantity,costperunit,totalcost)
      Values (1,@comp_name,@quantity,@costperunit,@quantity*@costperunit)

Answer 2

You need to use update.

UPDATE SALES SET totalcost=quantity*costperunit

Answer 3

It would be better if you do not calculate this field manually but make it a computed column instead - so it calculates automatically for you.

You can change the column with following query:

ALTER TABLE Sales
DROP COLUMN totalcost

ALTER TABLE Sales
ADD totalcost AS quantity*costperunit

DEMO

Answer 4

Try updating the table

UPDATE SALES SET totalcost=quantity*costperunit

Answer 5

Best not to store fields that can be calculated, but if you want to, with SQL Server you can set a field to be calculated automatically when values are there.