Обсуждение: correlated multi-set update?

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?

Thanks in advance.

Sincerely,
Marty

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.

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   );