Home > database >  How can I insert total price in orders table using (pizza_price*pizza_amount) exisiting in two diffe
How can I insert total price in orders table using (pizza_price*pizza_amount) exisiting in two diffe

Time:05-14

This is relational diagram of database I created.

I need to insert total price in total_price column of orders table, by multiplying the pizza_price value with pizza_amount. I'm a beginner so I don't have idea how actually to do it. Please Help.

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.

  • Related