From: Bruno Wolff III <bruno@wolff.to>
To: Marty Scholes <marty@outputservices.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: correlated multi-set update?
Date: Tue, 9 Mar 2004 22:52:09 -0600
Message-ID: <20040310045209.GC31629@wolff.to> (raw)
In-Reply-To: <404E3BC9.7050000@outputservices.com>
References: <404E3BC9.7050000@outputservices.com>
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
);
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: bruno@wolff.to, marty@outputservices.com
Subject: Re: correlated multi-set update?
In-Reply-To: <20040310045209.GC31629@wolff.to>
* 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