DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=I/XkI1ZzwONFOli9yI0vIwfiA2qtuQz9Z9MA9xOLFThA+dS+KEOLhvUYo460PzWuSJ
	GmoZ+2+IPWSuOIBElXxSfPsk7lUSv/Xx/J4esvNqtMGHii3ea9sa7IgjGzU9NRBhVjIq
	cU0+IfYMKMH27jh3xVfJ+zlNMAg4RH4NDkcbc=
MIME-Version: 1.0
Date: Thu, 14 Apr 2011 15:33:16 -0600
Message-ID: <BANLkTi=qq_OCFSPpJ-HvyZGXTc-eA-5e6Q@mail.gmail.com>
Subject: update with recursive query
From: Steven Dahlin <pgdb.sldahlin@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=e0cb4e88758f6473f404a0e7ac63

--e0cb4e88758f6473f404a0e7ac63
Content-Type: text/plain; charset=ISO-8859-1

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

--e0cb4e88758f6473f404a0e7ac63
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: base64

SXMgaXQgcG9zc2libGUgdG8gZXhlY3V0ZSBhbiB1cGRhdGUgdXNpbmcgcmVjdXJzaW9uP6AgSSBu
ZWVkIHRvIHVwZGF0ZSBhIHNldCBvZiByZWNvcmRzIGFuZCBhbHNvIHVwZGF0ZSB0aGVpciBjaGls
ZHJlbiB3aXRoIHRoZSBzYW1lIHZhbHVlLqAgSSB0cmllZCB0aGUgZm9sbG93aW5nIHF1ZXJ5IGJ1
dCBpdCBnYXZlIGFuIGVycm9yIGF0IHRoZSAmcXVvdDt1cGRhdGUgc2NoZW1hLnRhYmxlIHRibCZx
dW90Ozo8YnI+Cjxicj6goKAgd2l0aCByZWN1cnNpdmUgY2hpbGRUYmwoIHBpZCw8YnI+oKCgoKCg
oKCgoKCgoKCgoKCgoKCgoKCgoKCgoCBwcGlkLDxicj6goKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCg
oKCgIHByb2NfaWQsPGJyPqCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgb3RoZXJfaWQgKTxi
cj6goKCgIGFzoCAoIHNlbGVjdCBwcmMucGlkLDxicj6goKCgoKCgoKCgoKCgoKCgoCBwcmMucHBp
ZCw8YnI+CqCgoKCgoKCgoKCgoKCgoKCgIHByYy5wcm9jX2lkLDxicj6goKCgoKCgoKCgoKCgoKCg
oCBwcmMub3RoZXJfaWQ8YnI+oKCgoKCgoKCgoKAgZnJvbaAgbXlfc2NoZW1hLnByY190YmygoKCg
oKCgoKCgoKCgoCBwcmM8YnI+oKCgoKCgoKCgoKAgd2hlcmUgKCAoIHByYy5wcm9jX3BhdGigoKCg
oKCgoKAgbGlrZSAmIzM5OyVzdHVmZiUmIzM5OyApPGJyPqCgoKCgoKCgoKCgoKAgb3KgoKAgKCBw
cmMucHJvY19wYXJhbWV0ZXJzoKCgIGxpa2UgJiMzOTslc3R1ZmYlJiMzOTsgKSApPGJyPgqgoKCg
oKCgoKCgoKAgYW5koCAoIHByYy5vdGhlcl9pZCBpcyBudWxsICk8YnI+oKCgoKCgoKCgoCB1bmlv
biBhbGw8YnI+oKCgoKCgoKCgoCBzZWxlY3QgcHJjc3ViLnBpZCw8YnI+oKCgoKCgoKCgoKCgoKCg
oKAgcHJjc3ViLnBwaWQsPGJyPqCgoKCgoKCgoKCgoKCgoKCgIHByY3N1Yi5wcm9jX2lkLDxicj6g
oKCgoKCgoKCgoKCgoKCgoCBwcmNzdWIub3RoZXJfaWQ8YnI+oKCgoKCgoKCgoKAgZnJvbaAgY2hp
bGRUYmygoKCgoKCgoKCgoKCgoKCgoKCgoKCgoCBwcmNwYXIsPGJyPgqgoKCgoKCgoKCgoKCgoKCg
oCBteV9zY2hlbWEucHJjX3RibKCgoKCgoKCgoKCgoKCgIHByY3N1Yjxicj6goKCgoKCgoKCgoCB3
aGVyZSAoIHByY3N1Yi5wcGlkoKCgoKCgoKCgoKCgoKCgoCA9IHByY3Bhci5waWQgKTxicj6goKCg
oKCgoCApPGJyPqCgIHVwZGF0ZSBteV9zY2hlbWEucHJjX3RibKAgcHJjPGJyPqCgoKAgc2V0oKAg
b3RoZXJfaWSgoKCgoKAgPSAxMDE8YnI+oKCgoCBmcm9toCBjaGlsZFRibDxicj4KPGJyPkhvd2V2
ZXIsIGlmIEkgZG8gYSAmcXVvdDtzZWxlY3QgKiBmcm9tIGNoaWxkVGJsJnF1b3Q7IGl0IHdvcmtz
LqAgVGhlIGRvY3MgdGFrZSBhYm91dCB1cGRhdGVzIGFuZCB0YWxrIGFib3V0IHJlY3Vyc2l2ZSBx
dWVyaWVzIHdpdGggc2VsZWN0cyBidXQgbm90aGluZyBzZWVtcyB0byBjb3ZlciB0aGUgam9pbmlu
ZyBvZiB0aGUgdHdvLjxicj48YnI+VGhhbmtzPGJyPjxicj4K
--e0cb4e88758f6473f404a0e7ac63--