Need help with database cleanup (manual cascade delete)

[mwookie]

Hi all. I am trying to manual clean up a child table when parent table records are deleted.

I tried:


DELETE FROM agencyImage WHERE imageID IN
(SELECT images.imageID FROM images LEFT OUTER JOIN agencyImage ON
(images.imageID=agencyImage.imageID) WHERE agencyImage.imageID is null)

This failed because you can't delete from a table that you are looking at. So how do I delete the record from agencyImage table if no record exist in the parent images table?

thanks for the help.


"You cannot escape the responsibility of tomorrow by evading it today." Abraham Lincoln
Company (http://www.iriselements.com) – Projects (New Mexico Real Estate Listing (http://www.goNM.net) – Family Friendly Image Search (http://www.safepix.net))

[codeexploiter]

You are looking for a way to remove all those child records from child table when their corresponding parent records have been removed from the parent table.

As an example you have two tables DEPT and EMP where DEPT is acting as the parent table with the following structure

fieldname datatype Remarks
----------- ------------- ---------
deptid number Primary Key
deptname char

and you have a child table EMP with the following structure


fieldname datatype Remarks
----------- ------------- ---------
empid number Primary Key
empname char
deptid number Foreign Key


The SQL statement using which you can perform the manual cleanup in your child table once the parent records are remvoed is as follows:


DELETE FROM emp WHERE emp.deptid not in (SELECT deptid FROM dept);

If you check the above SQL statement you can find out that the inner SQL query will return all the deptid from dept table that are available there. The out SQL query will remove all those employee records who has a deptid which is not returned from the inner SQL query.

[mwookie]

Thanks. Worked perfectly.


____________
"You cannot escape the responsibility of tomorrow by evading it today." Abraham Lincoln
Company (http://www.iriselements.com) – Projects (New Mexico Real Estate Listing (http://www.goNM.net) – Family Friendly Image Search (http://www.safepix.net))