agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedProblem with self-join updates...
5+ messages / 3 participants
[nested] [flat]
* Problem with self-join updates...
@ 2002-02-15 18:39 Benoit Menendez <benoitm@pacbell.net>
2002-02-15 18:51 ` Re: Problem with self-join updates... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-19 18:32 ` Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
0 siblings, 2 replies; 5+ messages in thread
From: Benoit Menendez @ 2002-02-15 18:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-join updates...
2002-02-15 18:39 Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
@ 2002-02-15 18:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-02-15 18:51 UTC (permalink / raw)
To: Benoit Menendez <benoitm@pacbell.net>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Problem with self-join updates...
2002-02-15 18:39 Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
@ 2002-02-19 18:32 ` Benoit Menendez <benoitm@pacbell.net>
2002-02-19 19:06 ` Re: Problem with self-join updates... Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Benoit Menendez @ 2002-02-19 18:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-join updates...
2002-02-15 18:39 Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
2002-02-19 18:32 ` Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
@ 2002-02-19 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-19 19:35 ` Re: Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-02-19 19:06 UTC (permalink / raw)
To: Benoit Menendez <benoitm@pacbell.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-join updates...
2002-02-15 18:39 Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
2002-02-19 18:32 ` Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
2002-02-19 19:06 ` Re: Problem with self-join updates... Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-19 19:35 ` Benoit Menendez <benoitm@pacbell.net>
0 siblings, 0 replies; 5+ messages in thread
From: Benoit Menendez @ 2002-02-19 19:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-02-19 19:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-15 18:39 Problem with self-join updates... Benoit Menendez <benoitm@pacbell.net>
2002-02-15 18:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-19 18:32 ` Benoit Menendez <benoitm@pacbell.net>
2002-02-19 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-19 19:35 ` Benoit Menendez <benoitm@pacbell.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox