SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL - Find Multiple Entries, and Delete One based on the existance of another entry

    I have a table "fruitlist" that contains just two fields, "color" & "fruit".

    Example Entries:
    red : apple
    green : apple
    green : banana
    yellow : banana
    red : grapes
    green : grapes


    What I would like to do is delete the "GREEN" entry, ONLY IF the fruit has both a "GREEN" and a "RED" entry!
    In the example entries above I would like to delete the "Green Apple" and the "Green Grapes", but leave the "Green Banana" alone.

    I'm thinking I need to do an inner join, but not sure how to approach it.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    DELETE green
      FROM fruitlist AS green
    INNER
      JOIN fruitlist AS red
        ON red.fruit = green.fruit
       and red.color = 'red' 
     WHERE green.color = 'green'
    Off Topic:

    shout out to the 'peg
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you fellow Canadian for your help!


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
  •