SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    having trouble with an SQL statement

    I have the following tables (only relevant fields are shown):

    competitions
    id = competition ID

    portfolios
    id = portfolio ID
    comp_id = competition ID

    actions
    port_id = portfolio ID


    What I want to do is put together an SQL statement that, when given the competition ID, will delete all portfolios that have that comp_id and all actions have the port_id of one the deleted portfolios.

    That is, there is many portfolios for each competition. There is many actions for each portfolio (and therefore with each competition). I want to delete everything that has to do with the specified competition.

    Thanks for the help guys, I've been learning a lot on the SQL side of things from your help lately
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  2. #2
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh just so you know my guess would be this:

    DELETE
    FROM competitions, portfolios, actions
    WHERE competitions.id ='$comp_id'
    AND portfolios.comp_id = '$comp_id'
    AND actions.port_id = portfolios.id

    but I want to be sure I am correct
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jeanco View Post
    What I want to do is put together an SQL statement that, when given the competition ID, will delete all portfolios that have that comp_id and all actions have the port_id of one the deleted portfolios.
    if you were to define ON DELETE CASCADE for the foreign keys, then the query you are looking for is
    Code:
    delete
      from competitions
     where id = $comp_id  /* note no quotes around numbers */
    without ON DELETE CASCADE, you end up having to do all that work yourself
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937. Thats exactly what I ended up doing. Glad to see the same answer coming from more then one spot though.
    Ryan Price
    Subscribe to my blog for regular tips and tricks


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
  •