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
>
>
