Re: How to update a table with the result of deleting rows in another table

>
> 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 skip the "total" cte and just update the same rows repeatedly.
I'm not sure if the same row being repeatedly updated in the same statement
creates additional row versions or just updates the existing one.

> ...CTE’s act as optimisation fences.
>

It might be worth noting PG12 changes that behavior in simple cases where
the CTE is not recursive, not referenced more than once, and is side-effect
free.