automatically set the value of one variable depending on another variable's value in sql table

Question

I am new to sql and How can I automatically set the value of one variable depending other variable's value. I have price in foods table, and in orders table I want to change the total value of the price according to the # of orders for a specific food.

Answer 1

I can think of two approaches. The first of which is when the Order record has already been inserted into the database and you merely want to update its total price value:

UPDATE Order
Set TotalPrice = NumberOfItems * 
                 (SELECT Price FROM Food WHERE Food.FoodId = Order.FoodId)

Alternatively, you can grab the price of a food item when you insert the order into the database table:

-- Given: @FoodId and @NumberOfItems have been passed to this 
-- stored procedure as parameters
DECLARE @price DECIMAL(10, 2) -- or whatever your price is defined to be

SELECT @price = Price
FROM Food
WHERE FoodId = @FoodId

INSERT INTO ORDER(FoodId, NumberOfItems, TotalPrice)
VALUES
(@FoodId, @NumberOfItems, @NumberOfItems * @Price)