X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0B997D1BA93
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Mar 2004 23:14:28 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 95138-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Mar 2004 19:14:27 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 0398CD1B906
	for <pgsql-sql@postgresql.org>; Tue,  9 Mar 2004 19:14:25 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id D7E5035937; Tue,  9 Mar 2004 15:14:26 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id D65AC3592D; Tue,  9 Mar 2004 15:14:26 -0800 (PST)
Date: Tue, 9 Mar 2004 15:14:26 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Marty Scholes <marty@outputservices.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: correlated multi-set update?
In-Reply-To: <404E3BC9.7050000@outputservices.com>
Message-ID: <20040309151307.E7528@megazone.bigpanda.com>
References: <404E3BC9.7050000@outputservices.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/94
X-Sequence-Number: 17193


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.