'Update column "total price" in orders table using other columns (pizza_price*pizza_amount) in different tables?

This is relational diagram of database I created:

enter image description here

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?

mysqlsqldatabaseforeign-keys


Solution 1:[1]

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.

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pritey Mehta
Solution 2 VTi

Related Questions