> On 6 Oct 2020, at 7:37, Hemil Ruparel <hemilruparel2002@xxxxxxxxx> wrote: > > I am trying to delete orders for a given customer on a given date and add the cost of those orders to credit for the customer. > > So far, I came up with this: > ``` > with data as ( > delete from orders > where customer_id = <customer id> > and date = '2020-10-05' returning price > ), total as ( > select sum(price) from data > ) > update paymentdetail > set temp_credit = temp_credit + (select * from total) > where customer_id = <customer id> > ``` > > which works. but is there a better way to update one table using the result of deleting rows from another table given that I only want the aggregate of the result? Adding the customer id to your returning clause and using update..from could help: with data as ( delete from orders where customer_id = <customer id> returning customer_id, price ), total as ( select customer_id, sum(price) as total_price from data group by customer_id ) update paymentdetail set temp_credit = temp_credit + total.total_price from total where customer_id = total.customer_id You could also do this using subqueries instead of CTE’s, that may perform better as CTE’s act as optimisation fences. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
