SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2001
    Location
    Missouri
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DELETE with JOIN alternative in mySQL ver 3?

    Subject pretty much says it all. What's a work-around for doing a delete with a join if I'm stuck with mySQL 3?

    I want to accomplish something like:

    DELETE FROM Images, ImageInventory WHERE ImageInventory.InvId=$Id AND Images.Id=ImageInventory.ImageId

    --Shawn
    _____________________________
    Shawn Vickers
    http://www.networthyinc.com/

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If b is the "parent" table to c (and b has some parent a):

    1.
    select c.id
    from b inner join c on b.id = c.b_id
    where b.a_id = $id

    result:
    101
    102
    103

    2.
    implode the result to a string like "101,102,103" ($ids)

    3.
    delete from c where id in($ids)

    4.
    delete from b where a_id = $id

  3. #3
    SitePoint Member
    Join Date
    Jan 2001
    Location
    Missouri
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not pretty, but it'll have to do

    I was afraid you were going to suggest that method. I was hoping there was a "slicker" way, but I guess that's just the way I'll have to do it since I'm stuck with version 3. Thanks for the response.
    _____________________________
    Shawn Vickers
    http://www.networthyinc.com/

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another method is: Move to MS Access or Sql Server and create a "cascade delete" trigger


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
  •