Re: UPDATE tuples with a sub-select

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