Need to speed up a query

[mscertified]

I have a table of around 50,000 rows (table A) and another table of around
5000 rows (table B).
There is a foreign key in table A pointing to table B.
Periodically, table A records are archived leaving 'orphan' keys in table B.
I need a query that deletes the orphan rows in table B.
I tried DELETE * FROM TableB WHERE ID NOT IN (SELECT ClientID FROM TableA)
however, it takes forever to run.

 

[rox]

I have a table of around 50,000 rows (table A) and another table of around
5000 rows (table B).
There is a foreign key in table A pointing to table B.
Periodically, table A records are archived leaving 'orphan' keys in table
B.
I need a query that deletes the orphan rows in table B.
I tried DELETE * FROM TableB WHERE ID NOT IN (SELECT ClientID FROM TableA)
however, it takes forever to run.

Would SELECT DISTINCT speed things up any?

 

[Michael Gramelspacher]

I have a table of around 50,000 rows (table A) and another table of around
5000 rows (table B).
There is a foreign key in table A pointing to table B.
Periodically, table A records are archived leaving 'orphan' keys in table B.
I need a query that deletes the orphan rows in table B.
I tried DELETE * FROM TableB WHERE ID NOT IN (SELECT ClientID FROM TableA)
however, it takes forever to run.

maybe:

DELETE *
FROM TableB
WHERE TableB.ID IN (SELECT ID
FROM TableB LEFT JOIN TableA ON [TableB].[ID]=[TableA].[ClientID]
WHERE [TableA].[ClientID] Is Null);

 

[mscertified]

solution was to do a left outer join (described in Access help screens)