SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Deleting rows when using a join

    What is the best way to delete when a join is involved?

    Thanks!



    SELECT
    u.uID,
    s.searchDate,
    u.last,
    s.keyword,
    s.alerts,
    s.lastAlert,
    s.clicks,
    s.lastClick,
    s.timeStamp
    FROM users u
    INNER
    JOIN searches s
    ON u.uID = s.author
    WHERE
    (DATE_SUB(CURDATE(),INTERVAL 1825 DAY) > u.last) and clicks < 4
    ORDER BY last
    limit 1000;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Zealot Gar onn's Avatar
    Join Date
    Feb 2011
    Location
    Belgium
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select the ID of the row and then use 'DELETE ....... WHERE id =....'

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My join query produces hundreds of rows right now. I am seeking a solution that will allow me to delete them all at once. Thanks though.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SitePoint Zealot Gar onn's Avatar
    Join Date
    Feb 2011
    Location
    Belgium
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DELETE FROM ... WHERE id IN (select t1.id LEFT JOIN .... ON .... WHERE ....)

    something like that should work

  5. #5
    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)
    the SQL to do deletions using a join actually varies a great deal from one database system to another

    here at sitepoint, we have a databases forum and a mysql forum, so since you did not post in the mysql forum, could you tell us which database system you're running
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    mySQL
    Convert your dollars into silver coins. www.convert2silver.com

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Thread moved to MySQL forum
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok r937, I'm ready for your assistance.

    :-)
    Convert your dollars into silver coins. www.convert2silver.com

  9. #9
    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)
    Quote Originally Posted by busboy View Post
    Ok r937, I'm ready for your assistance.
    could you start by explaining which rows from which table you want to delete

    you've got users and searches

    but you never said which one you want to delete
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that. I'm wanting to delete from the searches table. I'm using the users table to see how long its been since they last logged in.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    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 searches
      FROM users
    INNER
      JOIN searches
        ON searches.author = users.uID 
       AND searches.clicks < 4
     WHERE users.last < CURRENT_DATE - INTERVAL 1825 DAY
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, excellent and thank you again.
    Convert your dollars into silver coins. www.convert2silver.com


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
  •