SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple table delete

    Hi

    I need some help writing a delete query which deletes data from 3 different tables - tried to read up about it but could quite make sense of it.

    table dives (main table)
    diveid, otherfields, profileid

    table profiles
    profileid, otherfields

    table profile_data (Index on profileid)
    dataid, profileid, otherfields

    Given a diveid, I want to delete the row from `dives` as well as the corresponding row in `profiles` (dives.profileid = profiles.profileid) along with all the corresponding rows in `profile_data`. (dives.profileid = profile_data.profileid)

    Thanks in advance!
    -------------------------------
    http://www.divinglogs.net
    The free online diving community

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What do you have so far?

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look up FOREIGN KEYS, add them to your tables (which will have to be INNODB type) and have deletes CASCADE. then you will only have to remove rows in your first table and the others will delete automatically.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look in the manual under delete syntax, there they give an example of multiple table delete. just like a join but you have to mention all tables at the beginning:

    Code:
    DELETE FROM
    table1, table2
    WHERE table1.somecolumn=table2.somecolumn
    AND somecolumn = 'something'

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I tried and didn't manage to get it to work, but will read up in the manual and play around with it a bit.
    -------------------------------
    http://www.divinglogs.net
    The free online diving community

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not show us what you tried and we can help you further.

    Also you should elaborate. Did you try adding the foreign keys or the multi table delete?


Bookmarks

Posting Permissions

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