update with recursive query
От | Steven Dahlin |
---|---|
Тема | update with recursive query |
Дата | |
Msg-id | BANLkTi=qq_OCFSPpJ-HvyZGXTc-eA-5e6Q@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: update with recursive query
|
Список | pgsql-sql |
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 />
В списке pgsql-sql по дате отправления: