Re: correlated multi-set update?

On Tue, 9 Mar 2004, Marty Scholes wrote:

> Hello,
>
> My company recently deployed Pg 7.4.1. on Solaris for an experimental
> project and is using the experience to evaluate its viability for
> migration from Oracle 7.0.
>
> While I like a lot of the features of Pg, one thing I noticed that
> "seems" to be missing is the ability to set multiple fields in an update
> using a correlated subquery.
>
> For example, I have a statement that copies fields from a template (bar)
> into another table (foo) based on a list of keys in a third table (keylist):
>
> UPDATE foo f
> SET (f1, f2, f3, f4, f5) = (
> SELECT f1, f2, f3, f4, f5
> FROM bar b
> WHERE f.fk = b.pk
> )
> WHERE f.pk IN (
> SELECT l.pk
> FROM keylist l
> );
>
> In Oracle this works wonders, but it seems to fail under Pg because Pg
> wants single field updates and does not allow subqueries.
>
> Next I tried:
>
> UPDATE foo f
> SET f1 = (
> SELECT f1
> FROM bar b
> WHERE f.fk = b.pk
> ),
> f2 = (
> SELECT f2
> FROM bar b
> WHERE f.fk = b.pk
> ),
> f3 = (
> SELECT f3
> FROM bar b
> WHERE f.fk = b.pk
> ),
> f4 = (
> SELECT f4
> FROM bar b
> WHERE f.fk = b.pk
> ),
> f5 = (
> SELECT f5
> FROM bar b
> WHERE f.fk = b.pk
> )
> WHERE f.pk IN (
> SELECT l.pk
> FROM keylist l
> );
>
> That seemed to get closer, but still barfed (apparently) because of a
> lack of table aliasing and correlated subqueries. This makes the
> process become an iterative one.
>
> Am I missing something here?

You're not allowed to alias the update target table, so I think you'd need
to remove the f alias and refer to foo anywhere you're currently referring
to f.