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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to