pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Benoit Menendez <benoitm@pacbell.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with self-join updates...
Date: Fri, 15 Feb 2002 10:51:03 -0800 (PST)
Message-ID: <20020215104908.Y37614-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <002101c1b650$18f087a0$0201a8c0@osprey>
On Fri, 15 Feb 2002, Benoit Menendez wrote:
> I have the following self-join update:
>
> update TABLE set PARENT_ID=parent.PARENT_ID
> from TABLE, TABLE parent
> where TABLE.PARENT_ID=parent.ID
> and parent.ID in (1,2,3,4)
>
> This query is use to update a hierarchy before deleting specific
> records...
>
> I get the following error:
>
> Table name "table" specified more than once
>
> This appears to be a limitation of the update syntax which is not
> documented...
>
> Is this something that will be fixed soon? or should I write this
> query differently?
The query above does a three way join of table, once for the
update table reference and once for each mention in from, which
probably isn't what you meant.
Maybe:
update table set PARENT_ID=parent.PARENT_ID
from TABLE parent
where TABLE.PARENT_ID=parent.ID
and parent.ID in (1,2,3,4)
view thread (5+ messages) latest in thread
Message-ID: <20020215104908.Y37614-100000@megazone23.bigpanda.com>
Permalink: ../20020215104908.Y37614-100000@megazone23.bigpanda.com/
Also on: postgresql.org/message-id/20020215104908.Y37614-100000@megazone23.bigpanda.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, benoitm@pacbell.net
Subject: Re: Problem with self-join updates...
In-Reply-To: <20020215104908.Y37614-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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