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
