UPDATE FROM syntax

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