How should I update a column based on the sum of products of another table?-CodePudding

Here I have 3 tables, CONTAINS, ORDER, and PRODUCT. The problem is that the total price of the order in the ORDER1 table must be calculated, it is the sum (product * price) for all the products in that order, so for example for Order1 ID = 1 in table ORDER1 we must go to the CONTAINS table to find the quanity of the product and barcode of the product (which we use to get the price of that product from the PRODUCT table). after that we multiply price by quantity for every product in that Order_ID to get the total price,

These are the tables:

Table: Order1

Order_ID	Total Price
1	NULL
2	NULL

Table: Contains

Order_ID	Barcode	Quantity
1	12	2
1	34	1
2	56	4

Table: Product

Barcode	Price
12	5
34	1
56	6

I know how to generate a table that contains the order_ID and the total price, but I do not know how to UPDATE the Order1 table using what I wrote, and I must use an UPDATE statement

This is how the select statement would generate the correct ouptput:

SELECT  ORDER1.ORDER_ID, SUM(Quantity*Selling_Price) AS "Total"
FROM PRODUCT, IS_PRESENT_IN, Order1
WHERE PRODUCT.BARCODE = IS_PRESENT_IN.BARCODE AND ORDER1.ORDER_ID = IS_PRESENT_IN.ORDER_ID 
GROUP BY order1.ORDER_ID
ORDER BY SUM(Quantity*Selling_price) ;

CodePudding user response：

This syntax would work, based on other examples I have seen. I haven't tested it:

UPDATE Order1
SET TotalPrice=
         (SELECT SUM(Quantity*Selling_Price)
          FROM PRODUCT P
               
               INNER JOIN 
               IS_PRESENT_IN IPI
               ON P.BARCODE=IPI.BARCODE

          WHERE IPI.ORDER_ID=Order1.ORDER_ID
          )

CodePudding user response：

merge does that job nicely:

SQL> merge into order1 o
  2  using (select i.order_id,
  3                sum(i.quantity * p.selling_price) as total_price
  4         from is_present_in i join product p on p.barcode = i.barcode
  5         group by i.order_id
  6        ) x
  7  on (x.order_id = o.order_id)
  8  when matched then update set
  9    o.total_price = x.total_price;

2 rows merged.

SQL> select * from order1;

  ORDER_ID TOTAL_PRICE
---------- -----------
         1          11
         2          24

SQL>