Обсуждение: UPDATE FROM syntax

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/

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