Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1FIp3E53033
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2002 13:51:03 -0500 (EST)
	(envelope-from sszabo@megazone23.bigpanda.com)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 6A419D5E5; Fri, 15 Feb 2002 10:51:03 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 5BB9F5BF9; Fri, 15 Feb 2002 10:51:03 -0800 (PST)
Date: Fri, 15 Feb 2002 10:51:03 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Benoit Menendez <benoitm@pacbell.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Problem with self-join updates...
In-Reply-To: <002101c1b650$18f087a0$0201a8c0@osprey>
Message-ID: <20020215104908.Y37614-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200202/188
X-Sequence-Number: 6614


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)