Hello and welcome to our community! Is this your first visit?
Register
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
    Location
    NM. USA
    Posts
    10
    Thanks
    1
    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:

    Code:
    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 ListingFamily Friendly Image Search)

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 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
    •