Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    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.

    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 Projects (New Mexico Real Estate Listing Family Friendly Image Search)

  2. #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    St. Catharines, Ontario Canada
    Thanked 156 Times in 147 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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts