Re: correlated multi-set update?

On Tue, Mar 09, 2004 at 14:48:57 -0700,
Marty Scholes <marty(at)outputservices(dot)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.pk IN (
SELECT l.pk
FROM keylist l
);