Massive Delete

[nexes128]

MSACESS 2000
FORM

Problem: I have two tables one which has approx 1 million records(tbl1) and another which has approx 400,000(tbl2). I am trying to remove entries in tbl1 that dont have a corresponding value in tbl2. I have tryed doing a sub query(DELETE FROM tbl1 where field1 NOT IN(select field1 from tbl2) but the system just freezes and doesnt actually delete anything.

I have come up with the idea that i can use two seperate loops and two recordsets to accomplish this but i need a way to stop the system from freeze while im doing this neideas?

[RobDog888]

you have aloot of records in your tables so it will be a strain on the system no matter what. It will depend upon your system resources but doing a subquery will take its toll.

Is this a one time operation or is it a maintenance service that you will be running often?

[nexes128]

well it will only be done every couple years cuz its like 3 years data im trying to remove from a fresh copy and the sub query just freezes and does nothing is there neother method of doing in to fix this problem



BTW my sys at work is a P4 2.8 GHz w/ 768MB ram and a 1gb swap

[RobDog888]

When it freezes its actually doing stuff but how long did you let it process?

[nexes128]

well it shows 1 bar then it will sit there for about 2 or 3 hours doin nothin the bar wont move nothin it will just go unresponsive

[RobDog888]

Yes, that is plenty of time to at least see it move a couple of bar segments.


What if you changed the query to do a select into instead of a delete. Then you will end up with a table of just the needed records. Then delete the entire original table.

[nexes128]

I have manage to get a select to work with a left join but that wont work on a delete, and the select sub query still wont work

[RobDog888]

No, I mean couldnt you do a select into where it will pull at the needed records into a new table. Then delete the previous table.