Peter
Basically try to do a join between A and B in your subquery.
BUT: Your db may not allow deleting from a table referenced in a subquery.
Solution: Run the subquery, store in a temp table then run the main query
linked to the temp table:
select distinct A.ID
from A, B
where A.ID = B.A_ID
into temp AList;
delete from A
where A.ID not in
(select AList.ID
from Alist);
Note:
Temp tables are usually very fast!
Cheers
Martin
-----Original Message-----
From: Peter Hyde [SMTP:[EMAIL PROTECTED]]
Sent: Friday, August 20, 1999 3:46 PM
To: Multiple recipients of list database
Subject: [DUG-DB]: sql to delete non-members of a union
Hi folks,
This will be simple for the experts, I hope. I have two tables,
one of which, "A" has an ID field, and another of which, "B" is a
join table which references A.ID.
I want to delete from "A" all items which are not currently
represented in B. I could use this SQL I think, but I'm conscious
of how SLOOOOOOW it would be, because the second query is
going to be run for EVERY row in A. Can anyone propose a more
efficient formulation please?
delete from A
where ID not in
(select A.ID from A, B
where B.A_ID=A.ID)
cheers,
peter
============================================
Peter Hyde, SPIS Ltd, Christchurch, New Zealand
* Print-to-Web automation http://TurboPress.com
* Web design, automation and hosting specialists
* TurboNote: http://TurboPress.com/tbnote.htm
-- small, FREE and very handy
Find all the above and MORE at http://www.spis.co.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
