View Full Version : Manual Cascade Delete implementation needed

02-19-2007, 04:17 AM
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))

02-19-2007, 03:05 PM
create a temporary table.

select all the items in your table that have parent ids into the temporary table.

drop your old table and then rename your temporary table to the dropped table name.

then add a constraint on your secondary table that points to the primary key of the main table, that way you won't add orphan records in the future. note this last bit only works if you are using innodb tables and not myisam tables.