[SQL] correlated multi-set update?

[Marty Scholes]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])