update using recursion

From: Steven Dahlin <pgdb(dot)sldahlin(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: update using recursion
Date: 2011-04-14 21:53:59
Message-ID: BANLkTi=cbcEfZofjCoUXgXrRd5Ews72BnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message LaraK 2011-04-15 06:20:30 convert in GMT time zone without summer time
Previous Message Steven Dahlin 2011-04-14 21:33:16 update with recursive query