From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Steven Dahlin <pgdb(dot)sldahlin(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: update with recursive query |
Date: | 2011-04-15 06:46:19 |
Message-ID: | BANLkTi=XCnec68pVrkkV=_hMT1BHdG4m8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)sldahlin(at)gmail(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2011-04-16 12:02:35 | Re: convert in GMT time zone without summer time |
Previous Message | LaraK | 2011-04-15 06:20:30 | convert in GMT time zone without summer time |