pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE FROM syntax
2+ messages / 2 participants
[nested] [flat]
* UPDATE FROM syntax
@ 2004-02-23 12:11 Paul Makepeace <postgresql.org@paulm.com>
0 siblings, 1 reply; 2+ messages in thread
From: Paul Makepeace @ 2004-02-23 12:11 UTC (permalink / raw)
To: pgsql-docs
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).
[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.)
Paul
(PS and now at least one working UPDATE FROM example is online ;-)
--
Paul Makepeace ................................ http://paulm.com/ecademy
"What is it i am yapping about? Three things: location, location,
location."
-- http://paulm.com/toys/surrealism/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: UPDATE FROM syntax
@ 2004-02-29 01:05 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Paul Makepeace <postgresql.org@paulm.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2004-02-29 01:05 UTC (permalink / raw)
To: Paul Makepeace <postgresql.org@paulm.com>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-29 01:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-23 12:11 UPDATE FROM syntax Paul Makepeace <postgresql.org@paulm.com>
2004-02-29 01:05 ` Bruce Momjian <pgman@candle.pha.pa.us>
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