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 ; 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 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
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)-- 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 ; 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 To: Benoit Menendez Cc: 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 postgres@vsservices.com Fri Feb 15 21:51:06 2002 Received: from primus.vsservices.com (primus.vsservices.com [63.66.136.75]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1FLp6E09850 for ; Fri, 15 Feb 2002 16:51:06 -0500 (EST) (envelope-from postgres@vsservices.com) Received: from prime.vsservices.com (conr-adsl-dhcp-26-38.txucom.net [209.34.26.38]) by primus.vsservices.com (8.11.3/8.11.3) with SMTP id g1FLp7k84701 for ; Fri, 15 Feb 2002 15:51:07 -0600 (CST) (envelope-from postgres@vsservices.com) Date: Fri, 15 Feb 2002 15:51:07 -0600 From: GB Clark To: pgsql-sql@postgresql.org Message-Id: <20020215155107.1e405665.postgres@vsservices.com> In-Reply-To: <20020215104908.Y37614-100000@megazone23.bigpanda.com> References: <002101c1b650$18f087a0$0201a8c0@osprey> <20020215104908.Y37614-100000@megazone23.bigpanda.com> X-Mailer: Sylpheed version 0.6.6 (GTK+ 1.2.10; i386-unknown-freebsd4.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/189 X-Sequence-Number: 6615 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? From postgres@vsservices.com Fri Feb 15 21:55:41 2002 Received: from primus.vsservices.com (primus.vsservices.com [63.66.136.75]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1FLtfE16359 for ; Fri, 15 Feb 2002 16:55:41 -0500 (EST) (envelope-from postgres@vsservices.com) Received: from prime.vsservices.com (conr-adsl-dhcp-26-38.txucom.net [209.34.26.38]) by primus.vsservices.com (8.11.3/8.11.3) with SMTP id g1FLtgk84757 for ; Fri, 15 Feb 2002 15:55:42 -0600 (CST) (envelope-from postgres@vsservices.com) Date: Fri, 15 Feb 2002 15:55:42 -0600 From: GB Clark To: pgsql-sql@postgresql.org Subject: Sorry about the no subject message... Message-Id: <20020215155542.0973924e.postgres@vsservices.com> In-Reply-To: <20020215155107.1e405665.postgres@vsservices.com> References: <002101c1b650$18f087a0$0201a8c0@osprey> <20020215104908.Y37614-100000@megazone23.bigpanda.com> <20020215155107.1e405665.postgres@vsservices.com> X-Mailer: Sylpheed version 0.6.6 (GTK+ 1.2.10; i386-unknown-freebsd4.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/190 X-Sequence-Number: 6616 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? From dgriffiths@boats.com Sat Feb 16 01:54:28 2002 Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1G1sSE68712 for ; Fri, 15 Feb 2002 20:54:28 -0500 (EST) (envelope-from news@jupiter.hub.org) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1G1pIT80569 for pgsql-sql@postgresql.org; Fri, 15 Feb 2002 19:51:18 -0600 (CST) (envelope-from news) From: "David Griffiths" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: SELECT... FOR UPDATE Date: Fri, 15 Feb 2002 17:49:49 -0800 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 50 Message-ID: References: <20020215155107.1e405665.postgres@vsservices.com> X-Complaints-To: news@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 To: pgsql-sql@postgresql.org X-Archive-Number: 200202/194 X-Sequence-Number: 6620 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" 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 From chriskl@familyhealth.com.au Sat Feb 16 04:41:25 2002 Received: from mail.iinet.net.au (symphony-01.iinet.net.au [203.59.3.33]) by postgresql.org (8.11.3/8.11.4) with SMTP id g1G4fPE83718 for ; Fri, 15 Feb 2002 23:41:25 -0500 (EST) (envelope-from chriskl@familyhealth.com.au) Received: (qmail 923 invoked by uid 666); 16 Feb 2002 04:41:26 -0000 Received: from unknown (HELO SOL) (203.59.159.98) by mail.iinet.net.au with SMTP; 16 Feb 2002 04:41:26 -0000 Message-ID: <00c701c1b6a3$83578500$0300a8c0@SOL> From: "Christopher Kings-Lynne" To: "GB Clark" , References: <002101c1b650$18f087a0$0201a8c0@osprey><20020215104908.Y37614-100000@megazone23.bigpanda.com> <20020215155107.1e405665.postgres@vsservices.com> Subject: Re: Date: Sat, 16 Feb 2002 12:36:30 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/195 X-Sequence-Number: 6621 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 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 ; 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 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
Can someone please help?
 
    Benoit
----- Original Message -----
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)-- 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 ; 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 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 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 X-Archive-Number: 200202/240 X-Sequence-Number: 6664 Benoit Menendez 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 ; 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 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" To: "Benoit Menendez" Cc: Sent: Tuesday, February 19, 2002 11:06 AM Subject: Re: [SQL] Problem with self-join updates... > Benoit Menendez 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