Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
02-19-2007, 04:17 AM #1
- Join Date
- Feb 2007
- NM. USA
- Thanked 0 Times in 0 Posts
Manual Cascade Delete implementation needed
Hi all. I am trying to manual clean up a child table when parent table records are deleted.
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)
thanks for the help.
"You cannot escape the responsibility of tomorrow by evading it today." Abraham Lincoln
Company – Projects (New Mexico Real Estate Listing – Family Friendly Image Search)
02-19-2007, 03:05 PM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 150 Times in 141 Posts
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.