Обсуждение: update with recursive query
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 />
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.sldahlin@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 > >