Tushar Tushar - 6 months ago 8
SQL Question

How to update a row value of one table by add mulitple row values of one table?

I have a sales table and a order table.

In sales table multiple rows having same order id.

What i need to update the order table amount by adding the amounts of sales table?
where order table id match with sales table order id?

sales table

ID | amount | order_id |
----------------------------
1 | 100 | 1 |
2 | 100 | 1 |
3 | 300 | 2 |
4 | 500 | 2 |


order table

ID | amount
---------------------
1 | 0.00
2 | 0.00


Result on order:

ID | amount
---------------------
1 | 200.00
2 | 800.00

Answer

You can use an update with a join and a subquery:

update orders o
join (select order_id, sum(amount) amount 
      from sales 
      group by order_id) s on o.id = s.order_id
set o.amount = s.amount
Comments