How to calculate the sum in one table and then insert into another table?-CodePudding

I have two tables. The first table: order

|id   |total|
|A001 |     |
|A002 |     |

The second table: goods

|id   |price|order_id|
|B001 |100  |A001    |
|B002 |200  |A001    |
|B003 |300  |A002    |

Now how to write the SQL statement to calculate the SUM price in goods table which have the same order_id then insert into order table in total column， like this:

|id   |total|
|A001 |300  |
|A002 |300  |

CodePudding user response：

Update O 
SET O.total = t.Sum_Of_Price
FROM Order AS O 
INNER JOIN 
    (
        Select order.id as ID, sum(goods.price) as Sum_Of_Price
        from goods 
        INNER JOIN order 
        ON order.id = goods.order_id  
        group by order.id
    ) t 
    ON O.id = t.id

CodePudding user response：

with temp(id, total) as (select order_id, sum(price) from goods group by order_id) update order o join temp t on o.id = t.id set o.total = t.total;

select * from order;

try it out here https://www.db-fiddle.com/f/caceQG8i4dWKZjUfhGRi2R/0