Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Dec 2005
    Posts
    35
    Thanks
    1
    Thanked 1 Time in 1 Post

    Comparing two tables and delete rows

    Hello everyone.

    I have two tables pluserdata and pluserlids. pluserdata contains all the users information such as ID username password email etc, and pluserlids contains just corresonding IDs (from pluserdata) and then leagueid which correspods to another table.

    What I am after is a query to look at pluserlids and then delete any rows that do not have a corresonding id in pluserdata.

    Another way of explaining it is that I have deleted about 1000 users (spam) from pluserdata but their ids are still in the pluserlids table so I want to check to see if they have been deleted then to remove their lid from pluserlids.

    Much thanks.

    Ryan


    EDIT: oh and I'm using MySQL 4.0.20
    Last edited by InsaneRhino; 03-25-2007 at 02:21 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can do an outer join on the two tables and where you find NULL, that's an orphaned row.

    This thread has a code example-- you would want to turn it into a DELETE query (but maybe do it as a SELECT query first to make sure the results are what you want to actually delete )

  • #3
    New Coder
    Join Date
    Dec 2005
    Posts
    35
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you very much my friend, I used the code below and it worked a treat. Much love.

    DELETE pluserlids. * FROM pluserlids LEFT JOIN pluserdata ON pluserlids.userid = pluserdata.userid WHERE pluserdata.userid IS NULL


  •  

    Posting Permissions

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