SQL - Update query only update one field

Question

I am trying to run an update query on my Stock table based on a certain barcode field.

Schema of Stock Table.

The query I am trying to run is

Update tblStock set Quantity = Quantity + 10, Total = Quantity * PPrice where barcode = 'shandar'

when Executing this query the Quantity Field gets updated but Total Field remains unchanged, I want to Multiply Quantity with PPrice.

Thanks in advance.

Answer 1

For your case, Once the first column "Quantity" is updated the update statement is not completed ,yet it has to update the Second Column "Total". So it takes the actual records from the table not the updated one. Else you can Split this into 2 updates for Different Columns.

Then You need to do like this:

Because, while updating the table the SQL Server considers the previous data until the code executes successfully without any Error.

This Frequently Updated/Deleted/Inserted rows/records can be viewed by using inserted & deleted tables by using Triggers

Update tblStock set Quantity = Quantity + 10, Total = (Quantity + 10)* PPrice 
where barcode = 'shandar'

Answer 2

You can use cross apply to define the new quantity in the FROM clause, and then use that value:

Update s
    set Quantity = v.Quantity,
        Total = v.Quantity * s.PPrice
    from tblStock s cross apply
         (values (s.Quantity + 10)) v(quantity)
    where s.barcode = 'shandar';

Repeating a simple operation such as quantity + 10 isn't a big deal. However, if the calculation is more complex, it is simpler to do it once.

That said, you might be better off with total as a computed column. Then it is just correct -- and never has to be updated. Drop it from the table and then re-add it as:

alter table tblStock add total as ( quantity * pprice );

Then the value of total is always correct, because it is calculated when you query the table.