Обсуждение: UPDATE tuples with a sub-select

Hi,

are there any plans to support updating a tuple using a sub-select in one of the future versions.

e.g, something like:

UPDATE report_table
   SET (order_count,order_value) = (SELECT count(*), sum(amount)
                                    FROM order o
                                    WHERE o.customer_id = report_table.customer_id);

I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on
the"roadmap" 

Regards
Thomas

On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote:
> Hi,
>
> are there any plans to support updating a tuple using a sub-select in one of the future versions.
>
> e.g, something like:
>
> UPDATE report_table
>   SET (order_count,order_value) = (SELECT count(*), sum(amount)
>                                    FROM order o
>                                    WHERE o.customer_id = report_table.customer_id);

What about:

UPDATE report_table SET order_count = s_count, order_value = s_value
FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
    WHERE o.customer_id = report_table.customer_id)

Its untested, but I think, it must works like this.

> I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on
the"roadmap" 
>
> Regards
> Thomas

Regards,
  Gerhard

Gerhard Heift, 07.11.2008 13:35:
>> are there any plans to support updating a tuple using a sub-select in one of the future versions.
>>
>> e.g, something like:
>>
>> UPDATE report_table
>>   SET (order_count,order_value) = (SELECT count(*), sum(amount)
>>                                    FROM order o
>>                                    WHERE o.customer_id = report_table.customer_id);
>
> What about:
>
> UPDATE report_table SET order_count = s_count, order_value = s_value
> FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
>     WHERE o.customer_id = report_table.customer_id)
>
> Its untested, but I think, it must works like this.
>

Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) )

But unfortunately it gives an error:

ERROR: subquery in FROM cannot refer to other relations of same query level [SQL State=42P10]

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> are there any plans to support updating a tuple using a sub-select in one of the future versions.

It's the first item under UPDATE on the TODO list ...

            regards, tom lane

On Fri, Nov 07, 2008 at 02:31:42PM +0100, Thomas Kellerer wrote:
> Gerhard Heift, 07.11.2008 13:35:
>>> are there any plans to support updating a tuple using a sub-select in one of the future versions.
>>>
>>> e.g, something like:
>>>
>>> UPDATE report_table
>>>   SET (order_count,order_value) = (SELECT count(*), sum(amount)
>>>                                    FROM order o
>>>                                    WHERE o.customer_id = report_table.customer_id);
>>
>> What about:
>>
>> UPDATE report_table SET order_count = s_count, order_value = s_value
>> FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
>>     WHERE o.customer_id = report_table.customer_id)
>>
>> Its untested, but I think, it must works like this.
>>
>
> Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) )
>
> But unfortunately it gives an error:
>
> ERROR: subquery in FROM cannot refer to other relations of same query
> level [SQL State=42P10]

Ok, its a little bit more complicated:

UPDATE report_table SET order_count = s_count, order_value = s_value
    FROM (SELECT customer_id, count(*) AS s_count, sum(amount) AS s_value
        FROM order o GROUP BY customer_id) AS summary
    WHERE summary.customer_id = report_table.customer_id)

> Regards
> Thomas

Regards,
  Gerhard

Gerhard Heift, 07.11.2008 14:47:
>>> What about:
>>>
>>> UPDATE report_table SET order_count = s_count, order_value = s_value
>>> FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
>>>     WHERE o.customer_id = report_table.customer_id)
>>>
>>> Its untested, but I think, it must works like this.
>>>
>> Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) )
>>
>> But unfortunately it gives an error:
>>
>> ERROR: subquery in FROM cannot refer to other relations of same query
>> level [SQL State=42P10]
>
> Ok, its a little bit more complicated:
>
> UPDATE report_table SET order_count = s_count, order_value = s_value
>     FROM (SELECT customer_id, count(*) AS s_count, sum(amount) AS s_value
>         FROM order o GROUP BY customer_id) AS summary
>     WHERE summary.customer_id = report_table.customer_id)
>

Cool! Thanks a lot

Thomas

Tom Lane, 07.11.2008 14:33:
> Thomas Kellerer <spam_eater@gmx.net> writes:
>> are there any plans to support updating a tuple using a sub-select in one of the future versions.
>
> It's the first item under UPDATE on the TODO list ...
>
That is good news :)

Thanks
Thomas

On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote:
> Hi,
>
> are there any plans to support updating a tuple using a sub-select in one of the future versions.
>
> e.g, something like:
>
> UPDATE report_table
>   SET (order_count,order_value) = (SELECT count(*), sum(amount)
>                                    FROM order o
>                                    WHERE o.customer_id = report_table.customer_id);

What about:

UPDATE report_table SET order_count = s_count, order_value = s_value
FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
    WHERE o.customer_id = report_table.customer_id)

Its untested, but I think, it must works like this.

> I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on
the"roadmap" 
>
> Regards
> Thomas

Regards,
  Gerhard