Delete SQL (Access 2002)

Topic

New Reply

Hi

A quick search of the forum hasn’t found the answer to this small problem.

I’m trying to write a delete sql which deletes records from two tables based on criteria from one of the tables. The table join is one to many relationship (see attahed). The following code is my initial attempt, which failed (error message said could not delete from table):

DELETE DISTINCTROW Active.*, NEDI.*
FROM Active INNER JOIN NEDI ON Active.Active_ID = NEDI.Active_ID
WHERE (((Active.ADI_WHO)=0) AND ((Active.ADI_ERMA)=0));

I know there are some ‘tricks’ to doing this, but I can’t recall what they are!

If further info required, please advise.

Thanks and Regards
WTH

Reply | Quote

Replies

Your query is trying to delete records from two tables at once. That is not possible. You must decide from which table you want to delete records.

Reply | Quote

Hi Hans

Thanks for the speedy reply. Can you suggest an alternative way to avoid a manual delete from the second table (NEDI) Obviously, deleting records from the first table (actives) is not going to be a problem, but this will leave orphan records in the table NEDI. The issue I’m trying to resolve is to delete the linked records in NEDI without doing this manually.

Thanks & Regards

WTH

Reply | Quote

According to your screenshot, turning on “Enforce Referential Integrity” and “Cascade Delete Related Records” for the relationship between Active and NEDI is not an option. Try this SQL:

DELETE NEDI.* FROM NEDI WHERE NEDI.NediID In (SELECT NEDI.NediID FROM NEDI INNER JOIN Active ON NEDI.ActiveID = Active.ActiveID WHERE Active.ADI_WHO=0 AND Active.ADI_ERMA=0)

This “trick” of using a subquery is useful when deleting records involving a join.

Reply | Quote

Hi Hans

It works perfectly, many thanks for the advice.

Regards
WTH

Reply | Quote

Hi Hans

It works perfectly, many thanks for the advice.

Regards
WTH

Reply | Quote

According to your screenshot, turning on “Enforce Referential Integrity” and “Cascade Delete Related Records” for the relationship between Active and NEDI is not an option. Try this SQL:

DELETE NEDI.* FROM NEDI WHERE NEDI.NediID In (SELECT NEDI.NediID FROM NEDI INNER JOIN Active ON NEDI.ActiveID = Active.ActiveID WHERE Active.ADI_WHO=0 AND Active.ADI_ERMA=0)

This “trick” of using a subquery is useful when deleting records involving a join.

Reply | Quote

Hi Hans

Thanks for the speedy reply. Can you suggest an alternative way to avoid a manual delete from the second table (NEDI) Obviously, deleting records from the first table (actives) is not going to be a problem, but this will leave orphan records in the table NEDI. The issue I’m trying to resolve is to delete the linked records in NEDI without doing this manually.

Thanks & Regards

WTH

Reply | Quote