Re: correlated multi-set update?

On Tue, Mar 09, 2004 at 14:48:57 -0700, Marty Scholes <marty@outputservices.com> wrote:
> 
> 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.

You can't set more than one column in one phrase, so you need to use
something like you tried next.
> 
> Next I tried:
> 
> UPDATE foo f

I don't believe you can use an alias on the update table. If you change
this and use foo instead of f in the subselects, then the update should
do what you want.

However it may be more efficient to use a FROM clause and do something
like:

UPDATE foo SET f1 = b.f1,   f2 = b.f2,   f3 = b.f3,   f4 = b.f4,   f5 = b.f5 FROM bar as b WHERE   foo.pk = b.pk AND
f.pkIN (     SELECT l.pk     FROM keylist l   );