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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Hemil Ruparel
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?

Reply | Threaded
Open this post in threaded view
|

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

Pankaj Jangid-2
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



Reply | Threaded
Open this post in threaded view
|

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

Alban Hertroys-4
In reply to this post by Hemil Ruparel

> On 6 Oct 2020, at 7:37, Hemil Ruparel <[hidden email]> 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.



Reply | Threaded
Open this post in threaded view
|

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

Michael Lewis
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.