Re: UPDATE tuples with a sub-select

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