Re: update with recursive query

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