Message-ID: <404E3BC9.7050000@outputservices.com>
Date: Tue, 09 Mar 2004 14:48:57 -0700
From: Marty Scholes <marty@outputservices.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US;
	rv:1.0.1) Gecko/20020920 Netscape/7.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: correlated multi-set update?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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