Is it possible to execute an update using recursion? I need to update a set of records and also update their children
withthe same value. I tried the following query but it gave an error at the "update schema.table tbl":<br /><br />
withrecursive childTbl( pid,<br /> ppid,<br /> proc_id,<br
/> other_id )<br /> as ( select prc.pid,<br /> prc.ppid,<br />
prc.proc_id,<br /> prc.other_id<br /> from
my_schema.prc_tbl prc<br /> where ( ( prc.proc_path like '%stuff%' )<br
/> or ( prc.proc_parameters like '%stuff%' ) )<br /> and ( prc.other_id is null )<br
/> union all<br /> select prcsub.pid,<br /> prcsub.ppid,<br />
prcsub.proc_id,<br/> prcsub.other_id<br /> from childTbl prcpar,<br
/> my_schema.prc_tbl prcsub<br /> where ( prcsub.ppid =
prcpar.pid)<br /> )<br /> update my_schema.prc_tbl prc<br /> set other_id = 101<br /> from
childTbl<br/><br />However, if I do a "select * from childTbl" it works. The docs take about updates and talk about
recursivequeries with selects but nothing seems to cover the joining of the two.<br /><br />Thanks<br /><br />
