Hello it is possible in 9.1. In older version you have to use a temp table.
Regards Pavel Stehule 2011/4/14 Steven Dahlin <pgdb.sldah...@gmail.com>: > Is it possible to execute an update using recursion? I need to update a set > of records and also update their children with the same value. I tried the > following query but it gave an error at the "update schema.table tbl": > > with recursive childTbl( pid, > ppid, > proc_id, > other_id ) > as ( select prc.pid, > prc.ppid, > prc.proc_id, > prc.other_id > from my_schema.prc_tbl prc > where ( ( prc.proc_path like '%stuff%' ) > or ( prc.proc_parameters like '%stuff%' ) ) > and ( prc.other_id is null ) > union all > select prcsub.pid, > prcsub.ppid, > prcsub.proc_id, > prcsub.other_id > from childTbl prcpar, > my_schema.prc_tbl prcsub > where ( prcsub.ppid = prcpar.pid ) > ) > update my_schema.prc_tbl prc > set other_id = 101 > from childTbl > > However, if I do a "select * from childTbl" it works. The docs take about > updates and talk about recursive queries with selects but nothing seems to > cover the joining of the two. > > Thanks > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql