From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Paul Makepeace <postgresql.org@paulm.com>
Cc: PostgreSQL Docs <pgsql-docs@postgresql.org>
Subject: Re: UPDATE FROM syntax
Date: Sat, 28 Feb 2004 20:05:41 -0500 (EST)
Message-ID: <200402290105.i1T15fO04750@candle.pha.pa.us> (raw)
In-Reply-To: <20040223121105.GO8784@mythix.realprogrammers.com>
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
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-docs@postgresql.org
Cc: pgman@candle.pha.pa.us, postgresql.org@paulm.com
Subject: Re: UPDATE FROM syntax
In-Reply-To: <200402290105.i1T15fO04750@candle.pha.pa.us>
* 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