Multiply two columns of one table and save the result in the third column of one table

Question

I m has a table

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

After submitting the costperunit values, the costperunit totalcost be calculated as "totalcost=quantity*costperunit".

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

I tried this:

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

But it failed!

Someone please help me with this. Thanks at Advance

Answer 1

Try updating the table.

 UPDATE SALES SET totalcost=quantity*costperunit 

Answer 2

You need to use the update.

 UPDATE SALES SET totalcost=quantity*costperunit 

Answer 3

try this by inserting a new line

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

Answer 4

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

Answer 5

It would be better if you do not calculate this field manually, but make a calculated column instead, so that it automatically calculates you.

You can change the column with the following query:

 ALTER TABLE Sales DROP COLUMN totalcost ALTER TABLE Sales ADD totalcost AS quantity*costperunit 

Demo