pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate with recursive query
2+ messages / 2 participants
[nested] [flat]
* update with recursive query
@ 2011-04-14 21:33 Steven Dahlin <pgdb.sldahlin@gmail.com>
2011-04-15 06:46 ` Re: update with recursive query Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Steven Dahlin @ 2011-04-14 21:33 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: update with recursive query
2011-04-14 21:33 update with recursive query Steven Dahlin <pgdb.sldahlin@gmail.com>
@ 2011-04-15 06:46 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2011-04-15 06:46 UTC (permalink / raw)
To: Steven Dahlin <pgdb.sldahlin@gmail.com>; +Cc: 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.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
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-04-15 06:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-14 21:33 update with recursive query Steven Dahlin <pgdb.sldahlin@gmail.com>
2011-04-15 06:46 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox