From: Marty Scholes <marty@outputservices.com>
To: pgsql-sql@postgresql.org
Subject: correlated multi-set update?
Date: Tue, 09 Mar 2004 14:48:57 -0700
Message-ID: <404E3BC9.7050000@outputservices.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marty@outputservices.com
Subject: Re: correlated multi-set update?
In-Reply-To: <404E3BC9.7050000@outputservices.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox