Update column "total price" in orders table using other columns (pizza_price*pizza

This is relational diagram of database I created:

I need to insert total price in Orders.Total_Price column by multiplying the Pizza.Pizza_Price value with Order.Pizza_Amount.

I'm a beginner so I don't have idea how actually to do it. Could you help me?

CodePudding user response：

You need an update with a JOIN statement for this.

UPDATE order
SET o.Total_Price = p.Pizza_Price * o.Pizza_Amount
FROM
  order AS o
INNER JOIN 
  Pizza AS p ON p.Pizza_Id = O.Pizza_id

CodePudding user response：

UPDATE order o ,pizza p
SET o.total_price = p.pizza_price * o.pizza_amount
where o.pizza_id = p.pizza_Id;

This query will update information from two different tables, and it will take less time if there are too many records.