Date: Tue, 9 Mar 2004 22:52:09 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Marty Scholes <marty@outputservices.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: correlated multi-set update?
Message-ID: <20040310045209.GC31629@wolff.to>
Mail-Followup-To: Marty Scholes <marty@outputservices.com>,
	pgsql-sql@postgresql.org
References: <404E3BC9.7050000@outputservices.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <404E3BC9.7050000@outputservices.com>
User-Agent: Mutt/1.5.6i

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
    );