SQL to remove all rows in table where Foreign key is no longer valid

[Angus Comber]

Hello

I have two tables connected by a unique key in primary table - which is
foreign key in subsidiary table. Main table called Site with unique SiteID
and other table is Reminders with a foreign key called SiteID. There may be
multiple rows in Reminders with same SiteID. So one to Many relationship.

I have some rows in Reminders which are no longer valid now because they
contain SiteID's - for sites that have been deleted.

What SQL would I run to remove all rows in Reminders which have no
associated SiteID in the site table?

Angus

 

[Guest]

Hi Angus,

Try this:

SELECT Reminders.SiteID
FROM Reminders LEFT JOIN Site ON Reminders.SiteID = Site.SiteID
WHERE (((Site.SiteID) Is Null));


There is an unmatched query wizard that will help you build this query. With
the Queries object selected, click on the New button. The unmatched query
wizard is the last item in the list.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Angus,

I realize that I gave you a SELECT query to see all unmatched records in the
Reminders table, which would allow you to manually delete them. If you want
to delete the records sight unseen, you can try the following DELETE query:

DELETE Reminders.SiteID, *
FROM Reminders
WHERE Reminders.SiteID IN (

SELECT Reminders.SiteID FROM Reminders
LEFT JOIN Site
ON Reminders.SiteID = Site.SiteID WHERE Site.SiteID Is Null);


I usually like to see what I'm about to delete, before actually doing so.
Enforcing Referential Integrity should help prevent this problem in the
future. If you want, you can add the Cascade Delete option, but that can be
kind of dangerous too.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Angus Comber]

Brilliant. Just what I need. Many thanks, it works. Angus

 

[Guest]

You're welcome. Glad I could help.

Good luck on your project.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________