Re: [PERFORM] Are JOINs allowed with DELETE FROM
Steven Rosenstein wrote: > > > > Hi Michael, > > Thank you for the link to the documentation page. I forgot to mention that > we're still using version 7.3. When I checked the 7.3 documentation for > DELETE, there was no mention of being able to use fields from different > tables in a WHERE clause. This feature must have been added in a > subsequent release of PostgreSQL. > > Gaetano & John: I *did* try your suggestion. However, there were so many > summary ID's returned (9810 to be exact) that the DELETE seemed to be > taking forever. 7.3 is affected by bad performances if you use IN. Transform the IN in an EXIST construct. If it'is an option for you upgrade you DB engine. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Are JOINs allowed with DELETE FROM
Many thanks to Gaetano Mendola and Tom Lane for the hints about using fields from other tables in a DELETE's WHERE clause. That was the magic bullet I needed, and my application is working as expected. --- Steve ___ Steven Rosenstein IT Architect/Developer | IBM Virtual Server Administration Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001 Text Messaging: 6463456978 @ mobile.mycingular.com Email: srosenst @ us.ibm.com "Learn from the mistakes of others because you can't live long enough to make them all yourself." -- Eleanor Roosevelt Tom Lane <[EMAIL PROTECTED] s> To Sent by: Steven Rosenstein/New pgsql-performance York/[EMAIL PROTECTED] [EMAIL PROTECTED] cc .org pgsql-performance@postgresql.org Subject Re: [PERFORM] Are JOINs allowed 02/06/2005 02:49 with DELETE FROM PM Steven Rosenstein <[EMAIL PROTECTED]> writes: > Thank you for the link to the documentation page. I forgot to mention that > we're still using version 7.3. When I checked the 7.3 documentation for > DELETE, there was no mention of being able to use fields from different > tables in a WHERE clause. This feature must have been added in a > subsequent release of PostgreSQL. No, it's been there all along, if perhaps not well documented. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Are JOINs allowed with DELETE FROM
Steven Rosenstein <[EMAIL PROTECTED]> writes: > Thank you for the link to the documentation page. I forgot to mention that > we're still using version 7.3. When I checked the 7.3 documentation for > DELETE, there was no mention of being able to use fields from different > tables in a WHERE clause. This feature must have been added in a > subsequent release of PostgreSQL. No, it's been there all along, if perhaps not well documented. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Are JOINs allowed with DELETE FROM
_
Steven Rosenstein
IT Architect/Developer | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com
"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt
Michael Fuhr
<[EMAIL PROTECTED]>
To
02/06/2005 12:50 Steven Rosenstein/New
PMYork/[EMAIL PROTECTED]
cc
pgsql-performance@postgresql.org
Subject
Re: [PERFORM] Are JOINs allowed
with DELETE FROM
On Sun, Feb 06, 2005 at 12:16:13PM -0500, Steven Rosenstein wrote:
>
> DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE
> collect_date='2005-02-05';
>
> But I keep getting a parser error. Am I not allowed to use JOINs in a
> DELETE statement, or am I just fat-fingering the SQL text somewhere.
See the documentation for DELETE:
http://www.postgresql.org/docs/8.0/static/sql-delete.html
If you intend to delete the date's record from the summary table,
then the detail table could use a foreign key constraint defined
with ON DELETE CASCADE. Deleting a record from summary would then
automatically delete all associated records in detail.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Are JOINs allowed with DELETE FROM
Gaetano Mendola wrote: Steven Rosenstein wrote: DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05'; You have to tell it what table you are deleting from. Select * from A join B is both tables. What you want to do is fix the where clause. DELETE FROM detail WHERE detail.sum_id in ( select id from summary ) AND collect_date='2005-02-05'; I'm guessing this should actually be DELETE FROM detail WHERE detail.sum_id in ( SELECT id FROM summary WHERE collect_date='2005-02-05' ); Otherwise you wouldn't really need the join. You have to come up with a plan that yields rows that are in the table you want to delete. The rows that result from select * from detail join summary, contain values from both tables. If you want to delete from both tables, I think this has to be 2 deletes. Probably best to be in a transaction. BEGIN; DELETE FROM detail WHERE ... DELETE FROM summary WHERE collect_date = '2005-02-05'; COMMIT; Regards Gaetano Mendola John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Are JOINs allowed with DELETE FROM
On Sun, Feb 06, 2005 at 12:16:13PM -0500, Steven Rosenstein wrote: > > DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE > collect_date='2005-02-05'; > > But I keep getting a parser error. Am I not allowed to use JOINs in a > DELETE statement, or am I just fat-fingering the SQL text somewhere. See the documentation for DELETE: http://www.postgresql.org/docs/8.0/static/sql-delete.html If you intend to delete the date's record from the summary table, then the detail table could use a foreign key constraint defined with ON DELETE CASCADE. Deleting a record from summary would then automatically delete all associated records in detail. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Are JOINs allowed with DELETE FROM
Steven Rosenstein wrote: DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05'; DELETE FROM detail WHERE detail.sum_id in ( select id from summary ) AND collect_date='2005-02-05'; Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
