Re: How to update a table with the result of deleting rows in another table - Mailing list pgsql-general

On Tue, Oct 06 2020, Hemil Ruparel wrote:


> 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>

Not sure about better way but will this also not work? I just removed
the second clause.

#+BEGIN_SRC sql
with data as (
    delete from orders
        where customer_id = <customer id>
    and date = '2020-10-05' returning price
)
update paymentdetail
set temp_credit = temp_credit + (select sum(price) from data)
where customer_id = <customer id>
#+END_SRC