From benoitm@pacbell.net Fri Feb  15 18:39:12 2002
Received: from mta6.snfc21.pbi.net (mta6.snfc21.pbi.net [206.13.28.240])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1FIdCE50428
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2002 13:39:12 -0500 (EST)
	(envelope-from benoitm@pacbell.net)
Received: from osprey ([209.232.53.45])
	by mta6.snfc21.pbi.net (iPlanet Messaging Server 5.1 (built May 7
	2001)) with ESMTP id <0GRL00JQI7TBOU@mta6.snfc21.pbi.net> for
	pgsql-sql@postgresql.org; Fri, 15 Feb 2002 10:39:12 -0800 (PST)
Date: Fri, 15 Feb 2002 10:39:21 -0800
From: Benoit Menendez <benoitm@pacbell.net>
Subject: Problem with self-join updates...
To: pgsql-sql@postgresql.org
Message-id: <002101c1b650$18f087a0$0201a8c0@osprey>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
Content-type: multipart/alternative;
	boundary="Boundary_(ID_gAYc62TbrPRigepRZsxuvQ)"
X-Priority: 3
X-MSMail-priority: Normal
X-Archive-Number: 200202/187
X-Sequence-Number: 6613

This is a multi-part message in MIME format.

--Boundary_(ID_gAYc62TbrPRigepRZsxuvQ)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

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?

Any suggestions?

Thanks for your help.

    Benoit

--Boundary_(ID_gAYc62TbrPRigepRZsxuvQ)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2713.1100" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>I have the following self-join update:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial 
size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
update&nbsp;TABLE set 
PARENT_ID=parent.PARENT_ID<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
from TABLE, 
TABLE&nbsp;parent<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
where 
TABLE.PARENT_ID=parent.ID<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
and parent.ID in (1,2,3,4)<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>This query is use to update a hierarchy before 
deleting specific records...</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I get the following error:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; Table name "table" specified 
more than once</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>This appears to be a limitation of the update 
syntax which is not documented...</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Is this something that will be fixed soon? or 
should I write this query differently?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Any suggestions?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Thanks for your help.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; 
Benoit</DIV></FONT></BODY></HTML>

--Boundary_(ID_gAYc62TbrPRigepRZsxuvQ)--



From sszabo@megazone23.bigpanda.com Fri Feb  15 18:51:03 2002
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)





From benoitm@pacbell.net Tue Feb  19 18:32:11 2002
Received: from mta6.snfc21.pbi.net (mta6.snfc21.pbi.net [206.13.28.240])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1JIWAE54277
	for <pgsql-sql@postgresql.org>; Tue, 19 Feb 2002 13:32:11 -0500 (EST)
	(envelope-from benoitm@pacbell.net)
Received: from osprey ([209.232.53.52])
	by mta6.snfc21.pbi.net (iPlanet Messaging Server 5.1 (built May 7
	2001)) with ESMTP id <0GRS000JPM5LKP@mta6.snfc21.pbi.net> for
	pgsql-sql@postgresql.org; Tue, 19 Feb 2002 10:32:10 -0800 (PST)
Date: Tue, 19 Feb 2002 10:32:06 -0800
From: Benoit Menendez <benoitm@pacbell.net>
Subject: Problem with self-join updates...
To: pgsql-sql@postgresql.org
Message-id: <002b01c1b973$bf48c740$0201a8c0@osprey>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
Content-type: multipart/alternative;
	boundary="Boundary_(ID_N/eZgfhdhbaSED+1fpQkig)"
X-Priority: 3
X-MSMail-priority: Normal
References: <002101c1b650$18f087a0$0201a8c0@osprey>
X-Archive-Number: 200202/239
X-Sequence-Number: 6663

This is a multi-part message in MIME format.

--Boundary_(ID_N/eZgfhdhbaSED+1fpQkig)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

Can someone please help?

    Benoit
  ----- Original Message ----- 
  From: Benoit Menendez 
  To: pgsql-sql@postgresql.org 
  Sent: Friday, February 15, 2002 10:39 AM
  Subject: [SQL] Problem with self-join updates...


  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?

  Any suggestions?

  Thanks for your help.

      Benoit

--Boundary_(ID_N/eZgfhdhbaSED+1fpQkig)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2713.1100" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Can someone please help?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; Benoit</FONT></DIV>
<BLOCKQUOTE dir=ltr 
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV 
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> 
  <A title=benoitm@pacbell.net href="mailto:benoitm@pacbell.net">Benoit 
  Menendez</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>To:</B> <A title=pgsql-sql@postgresql.org 
  href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, February 15, 2002 10:39 
  AM</DIV>
  <DIV style="FONT: 10pt arial"><B>Subject:</B> [SQL] Problem with self-join 
  updates...</DIV>
  <DIV><BR></DIV>
  <DIV><FONT face=Arial size=2>I have the following self-join 
  update:</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial 
  size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  update&nbsp;TABLE set 
  PARENT_ID=parent.PARENT_ID<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  from TABLE, 
  TABLE&nbsp;parent<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  where 
  TABLE.PARENT_ID=parent.ID<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  and parent.ID in (1,2,3,4)<BR></FONT></DIV>
  <DIV><FONT face=Arial size=2>This query is use to update a hierarchy before 
  deleting specific records...</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>I get the following error:</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; Table name "table" specified 
  more than once</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>This appears to be a limitation of the update 
  syntax which is not documented...</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>Is this something that will be fixed soon? or 
  should I write this query differently?</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>Any suggestions?</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>Thanks for your help.</FONT></DIV>
  <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; 
Benoit</DIV></BLOCKQUOTE></FONT></BODY></HTML>

--Boundary_(ID_N/eZgfhdhbaSED+1fpQkig)--



From tgl@sss.pgh.pa.us Tue Feb  19 19:06:51 2002
Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1JJ6pE62569
	for <pgsql-sql@postgresql.org>; Tue, 19 Feb 2002 14:06:51 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1JJ6kS06779;
	Tue, 19 Feb 2002 14:06:46 -0500 (EST)
To: Benoit Menendez <benoitm@pacbell.net>
cc: pgsql-sql@postgresql.org
Subject: Re: Problem with self-join updates... 
In-reply-to: <002b01c1b973$bf48c740$0201a8c0@osprey> 
References: <002101c1b650$18f087a0$0201a8c0@osprey>
	<002b01c1b973$bf48c740$0201a8c0@osprey>
Comments: In-reply-to Benoit Menendez <benoitm@pacbell.net>
	message dated "Tue, 19 Feb 2002 10:32:06 -0800"
Date: Tue, 19 Feb 2002 14:06:46 -0500
Message-ID: <6776.1014145606@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200202/240
X-Sequence-Number: 6664

Benoit Menendez <benoitm@pacbell.net> writes:
>   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)

>       Table name "table" specified more than once

Wasn't this answered already?  You should not have the "from TABLE"
in there.  Essentially, there's already an implicit FROM entry for
the target table, you don't need another.  "from TABLE parent"
is sufficient here.

			regards, tom lane



From benoitm@pacbell.net Tue Feb  19 19:35:35 2002
Received: from mta7.pltn13.pbi.net (mta7.pltn13.pbi.net [64.164.98.8])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1JJZZE79132
	for <pgsql-sql@postgresql.org>; Tue, 19 Feb 2002 14:35:35 -0500 (EST)
	(envelope-from benoitm@pacbell.net)
Received: from Kestrel ([209.232.53.88])
	by mta7.pltn13.pbi.net (iPlanet Messaging Server 5.1 (built May 7
	2001)) with ESMTP id <0GRS00MGUP3BD8@mta7.pltn13.pbi.net> for
	pgsql-sql@postgresql.org; Tue, 19 Feb 2002 11:35:35 -0800 (PST)
Date: Tue, 19 Feb 2002 11:35:32 -0800
From: Benoit Menendez <benoitm@pacbell.net>
Subject: Re: Problem with self-join updates...
To: pgsql-sql@postgresql.org
Message-id: <001901c1b97c$98828b60$0d01a8c0@Kestrel>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT
X-Priority: 3
X-MSMail-priority: Normal
References: <002101c1b650$18f087a0$0201a8c0@osprey>
	<002b01c1b973$bf48c740$0201a8c0@osprey> <6776.1014145606@sss.pgh.pa.us>
X-Archive-Number: 200202/241
X-Sequence-Number: 6665

Thanks, this works... I did not know about the implicit table reference...

Now, I'm waiting for outer join updates and deletes so I don't have to use
in and sub-selects...

PostgreSQL rules...

    Benoit

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Benoit Menendez" <benoitm@pacbell.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 19, 2002 11:06 AM
Subject: Re: [SQL] Problem with self-join updates...


> Benoit Menendez <benoitm@pacbell.net> writes:
> >   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)
>
> >       Table name "table" specified more than once
>
> Wasn't this answered already?  You should not have the "from TABLE"
> in there.  Essentially, there's already an implicit FROM entry for
> the target table, you don't need another.  "from TABLE parent"
> is sufficient here.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html