Re: UPDATE tuples with a sub-select

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