From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200402290105.i1T15fO04750@candle.pha.pa.us>
Subject: Re: UPDATE FROM syntax
In-Reply-To: <20040223121105.GO8784@mythix.realprogrammers.com>
To: Paul Makepeace <postgresql.org@paulm.com>
Date: Sat, 28 Feb 2004 20:05:41 -0500 (EST)
Cc: PostgreSQL Docs <pgsql-docs@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Paul Makepeace wrote:
> Hi, is there any documentation for Pg's UPDATE FROM (apparently
> non-standard) syntax? It is mentioned briefly in \h update and not at
> all in http://www.postgresql.org/docs/7.3/static/dml-update.html
> or http://www.postgresql.org/docs/7.3/static/queries-table-expressions.html
> (directly).

Yea, it is kind of special for UPDATE, so we just have it in the UPDATE
manual page.

> [moving a bit OT for docs?]
> 
> I solved my own problem but was still curious since it's non-standard if
> there's a "more standard" way of updating a table from another query.
> 
>     ALTER TABLE components ADD COLUMN default_body TEXT;
> 
>     UPDATE components SET default_body = bb.body
>     FROM (SELECT b.body, cpb.component_id
>             FROM componentspagesbodies cpb NATURAL JOIN bodies b
>            WHERE cpb.page_id IS NULL) AS bb
>     WHERE bb.component_id = components.component_id;
> 
> (Incidently, I tried UPDATE components c SET ... c.component_id but the
> parser wasn't happy with it.)

No, there really isn't.  The only standard way I know of is to use a
subselect in the SET clause:

	SET val = (SELECT ...)

The big downside of this is the the SELECT is run for every query, and
it can't be used as part of the WHERE clause.  (You would need another
subquery there.)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073