Delete query to remove orphan records (Access 97)

Topic

New Reply

I have an Access 97 database which was set up without relationships between tables. Unfortunately, I now have orphan records in one table which don’t have a corresponding match in a second table. I need an SQL statement that will run a Delete Query to remove these records.

The common field between the 2 tables is ‘ExtensionID’. TableA contains records with ExtensionID’s that have no corresponding match in TableB.

Help, please!!!

Reply | Quote

Replies

Try

DELETE tblA.*, tblA.PrimaryKey
FROM tblA
WHERE (((tblA.PrimaryKey) Not In (select ForeignKey FROM tblB)));

Where PrimaryKey and ForeignKey are the fields that you would join on.

Reply | Quote

Here’s an alternative query:

DELETE DISTINCTROW TableA.*
FROM TableA LEFT JOIN TableB ON TableA.ExtensionID = TableB.ExtensionID
WHERE (((TableB.ExtensionID) Is Null));

Reply | Quote