Also if the list of values returned by the subquery is large try doing it
the other way - then hopefully the subquery takes less time to run on each
iteration:
delete from A where ID in
(select B.A_ID from A A2 RIGHT OUTER JOIN B
ON B.A_ID=A2.ID where A2.ID IS NULL)
the syntax is "off the top of my head" - (handing should be right? - "where
A2.ID IS NULL" may be dead-in-the-water).
Max
[Kerry said]
Is the reason it performs so poorly because you're repeating the use of the
'A' table, and so created a correlated sub-query?
What if you recoded it like this:
delete from A
where ID not in
(select A2.ID from A A2, B
where B.A_ID=A2.ID)
Just a thought.
Kerry S
*********** REPLY SEPARATOR ***********
On 8/20/99 at 3:46 PM Peter Hyde wrote:
>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
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
