Re: [SQL] RE: Help with query. (*) - Mailing list pgsql-general

From Tom Lane
Subject Re: [SQL] RE: Help with query. (*)
Date
Msg-id 22275.979780043@sss.pgh.pa.us
Whole thread Raw
List pgsql-general
"Diehl, Jeffrey" <jdiehl@sandia.gov> writes:
> Well, actually, I want to eventually delete the records from A if there is
> an entry in B.  That's why I am trying to use such a screwed up query. ;^)

If you don't mind being nonstandard, you could still do it in join
style:

DELETE FROM a WHERE a.x = b.x AND blah blah blah ...

Under Postgres this will form a join between A and B same as if you'd
said SELECT FROM a,b WHERE a.x = b.x etc, and then delete the rows of
A that are matched in the join.

If you want to be bog-SQL-standard then you have to use the WHERE EXISTS
construct that Josh mentioned.  Unfortunately, that's likely to be a
good deal slower (for large tables) under current releases of Postgres.
We have hopes of bringing the performance of the EXISTS variant up to
something close to the explicit join, but it's a version or two away
yet.

            regards, tom lane

pgsql-general by date:

Previous
From: GH
Date:
Subject: Re: viewing foreign key constraints
Next
From: Tom Lane
Date:
Subject: Re: postgres 7.0.3 core dumps