pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcorrelated multi-set update?
3+ messages / 3 participants
[nested] [flat]
* correlated multi-set update?
@ 2004-03-09 21:48 Marty Scholes <marty@outputservices.com>
2004-03-09 23:14 ` Re: correlated multi-set update? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-10 04:52 ` Re: correlated multi-set update? Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 3+ messages in thread
From: Marty Scholes @ 2004-03-09 21:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: correlated multi-set update?
2004-03-09 21:48 correlated multi-set update? Marty Scholes <marty@outputservices.com>
@ 2004-03-09 23:14 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-03-09 23:14 UTC (permalink / raw)
To: Marty Scholes <marty@outputservices.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: correlated multi-set update?
2004-03-09 21:48 correlated multi-set update? Marty Scholes <marty@outputservices.com>
@ 2004-03-10 04:52 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2004-03-10 04:52 UTC (permalink / raw)
To: Marty Scholes <marty@outputservices.com>; +Cc: pgsql-sql
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.pk IN (
SELECT l.pk
FROM keylist l
);
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-03-10 04:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 21:48 correlated multi-set update? Marty Scholes <marty@outputservices.com>
2004-03-09 23:14 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-10 04:52 ` Bruno Wolff III <bruno@wolff.to>
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