How to delete records based on another table

[sumdumgai]

Thanks for any help here. I have two tables, call them bigTab and smallTab. Both tables have a field called f1. I want to delete all records from smallTab where there is not a matching f1 in bigTab. I've tried various versions of the following delete query but all seem to take forever to complete:

Delete from smallT where f1 not in (select f1 from bigT).

Thanks.

 

[ByteMyzer]

You might try something like:

DELETE FROM smallTab
WHERE NOT EXISTS (
    SELECT f1
    FROM bigTab
    WHERE bigTab.f1 = smallTab.f1);

 

[arnelgp]

DELETE smallTab.* FROM smallTab LEFT JOIN bigTab ON smallTab.F1 = bigTag.F1 WHERE bigTab.F1 IS NULL;


also this one:


DELETE * FROM smallTab WHERE smallTab.F1 NOT IN (SELECT F1 FROM bigTab);


backup your smallTab table before doing this.

 

[sumdumgai]

Thanks for your suggestion. I'm still having the performance problem, though. The progress bar goes quickly to about 25% and then stalls. One more thing about these tables. The smallTab has unique f1 values. The bigTab, however, could have hundreds of f1 occurrences. So it seems that selection set would have many duplicate f1's and the query is looking at every one. Is there a more efficient way? Thanks.

 

[arnelgp]

DELETE * FROM smallTab WHERE smallTab.F1 NOT IN (SELECT DISTINCT F1 FROM bigTab);

 

[sumdumgai]

Thanks. That worked a little better. There's one more thing about the bigTab. The f1's are clustered, meaning that all of the unique f1 values are loaded at the same time; e.g., if the first f1 = 1 , then subsequent records are 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, and so on. So, once the query finds an f1 that should be deleted, isn't there a way to delete all of the f1's having that value without searching for the next equal f1?

 

[arnelgp]

on our sub query, we already eliminate the extra f1 with same values. we only get the unique values of f1 for all the records.

 

[sergey123]

we have price table and goods table in access we need to make query to delete all price values from table price that absent in table goods and leave all prices in table price that exist in table goods.
this code only update values how do we remake our program (delete valuers from Price.Present that absent in [Goods].Remnant )?**
UPDATE Price INNER JOIN Goods ON [Price].Article = [Goods].Article SET Price.Present = [Goods].Remnant;