agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedProblem with self-join updates...
9+ messages / 6 participants
[nested] [flat]
* Problem with self-join updates...
@ 2002-02-15 18:39 Benoit Menendez <benoitm@pacbell.net>
0 siblings, 2 replies; 9+ 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] 9+ messages in thread
* Re: Problem with self-join updates...
@ 2002-02-15 18:51 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Benoit Menendez <benoitm@pacbell.net>
1 sibling, 1 reply; 9+ 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] 9+ messages in thread
* (no subject)
@ 2002-02-15 21:51 GB Clark <postgres@vsservices.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 3 replies; 9+ messages in thread
From: GB Clark @ 2002-02-15 21:51 UTC (permalink / raw)
To: pgsql-sql
Hi,
Can anyone give me a couple of examples or a explanation of select for update is supposed to work?
I've looked in the manual and the locking stuff is confusing (or it could just be me not understanding..))
and neither of my SQL books go into any type of detail on locking.
What I want to do is select a record, do something and then update that record to reflect the outcome
of the processing with 0% chance of another process doing the same thing. Is this possiable?
my idea:
BEGIN
SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
-- Do processing here
UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
COMMIT
Just looking for more information....
Thanks,
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Sorry about the no subject message...
@ 2002-02-15 21:55 GB Clark <postgres@vsservices.com>
parent: GB Clark <postgres@vsservices.com>
2 siblings, 0 replies; 9+ messages in thread
From: GB Clark @ 2002-02-15 21:55 UTC (permalink / raw)
To: pgsql-sql
Sorry about that last message not having a subject...
Got to turn that on!
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SELECT... FOR UPDATE
@ 2002-02-16 01:49 David Griffiths <dgriffiths@boats.com>
parent: GB Clark <postgres@vsservices.com>
2 siblings, 0 replies; 9+ messages in thread
From: David Griffiths @ 2002-02-16 01:49 UTC (permalink / raw)
To: pgsql-sql
SELECT .... FOR UPDATE;
locks all records returned from the SELECT. Anyone who tries to update any
one of those records will block until you commit, rollback (or the
connection is dropped and a rollback issued).
It will ensure that no-one can modifiy the record(s).
David
"GB Clark" <postgres@vsservices.com> wrote in message
news:20020215155107.1e405665.postgres@vsservices.com...
> Hi,
>
> Can anyone give me a couple of examples or a explanation of select for
update is supposed to work?
>
> I've looked in the manual and the locking stuff is confusing (or it could
just be me not understanding..))
> and neither of my SQL books go into any type of detail on locking.
>
> What I want to do is select a record, do something and then update that
record to reflect the outcome
> of the processing with 0% chance of another process doing the same thing.
Is this possiable?
>
> my idea:
>
> BEGIN
> SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
> -- Do processing here
> UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
> COMMIT
>
> Just looking for more information....
>
> Thanks,
>
> GB
>
> --
> GB Clark II | Roaming FreeBSD Admin
> gclarkii@VSServices.COM | General Geek
> CTHULU for President - Why choose the lesser of two evils?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
@ 2002-02-16 04:36 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: GB Clark <postgres@vsservices.com>
2 siblings, 0 replies; 9+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-16 04:36 UTC (permalink / raw)
To: GB Clark <postgres@vsservices.com>; pgsql-sql
Hi GB,
> BEGIN
> SELECT * FROM table1 WHERE record_id = '290202' FOR UPDATE
> -- Do processing here
> UPDATE table1 SET flag1 = '11' WHERE record_id = '290202'
> COMMIT
This SQL is correct and will work as you expect.
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Problem with self-join updates...
@ 2002-02-19 18:32 Benoit Menendez <benoitm@pacbell.net>
parent: Benoit Menendez <benoitm@pacbell.net>
1 sibling, 1 reply; 9+ 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] 9+ messages in thread
* Re: Problem with self-join updates...
@ 2002-02-19 19:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Benoit Menendez <benoitm@pacbell.net>
0 siblings, 1 reply; 9+ 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] 9+ messages in thread
* Re: Problem with self-join updates...
@ 2002-02-19 19:35 Benoit Menendez <benoitm@pacbell.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ 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] 9+ messages in thread
end of thread, other threads:[~2002-02-19 19:35 UTC | newest]
Thread overview: 9+ 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-15 21:51 ` GB Clark <postgres@vsservices.com>
2002-02-15 21:55 ` Sorry about the no subject message... GB Clark <postgres@vsservices.com>
2002-02-16 01:49 ` Re: SELECT... FOR UPDATE David Griffiths <dgriffiths@boats.com>
2002-02-16 04:36 ` Re: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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