Thread: UPDATE FROM syntax

[this didn't apparently make it thru' 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).

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:
> [this didn't apparently make it thru' 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).
>
> 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 ;-)

Try the UPDATE reference page in the docs.

--
  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