Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: delete duplicate records

    hi guys

    have this situation where i have to delete all duplicate records leaving only the lowest email id

    i have this query which gives me all the duplicate records but how could i delete it all except the one that has the lowest emailid of each duplicate.


    (SELECT S1.*
    FROM cl_email_temp AS S1
    JOIN
    (SELECT emailaddr
    FROM cl_email_temp
    GROUP BY emailaddr
    HAVING COUNT(*)>1) AS S2
    ON S1.emailaddr = S2.emailaddr)

    with this it gives me a list of all the duplicates the following is a sample result

    emailid emailaddr
    6360 live@email.com
    6361 live@email.com
    4451 a9940172@sample.com
    5022 a9940172@sample.com

    after deletion the remaining would be below which is the email address with has the lowest emailid

    emailid emailaddr
    6360 live@email.com
    4451 a9940172@sample.com

    I'm stuck with this and don't know how to proceed

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Air code:
    Code:
    SELECT S1.*
    FROM cl_email_temp AS S1
    JOIN
    (SELECT emailaddr, min_emailid = MIN(emailid)
    FROM cl_email_temp
    GROUP BY emailaddr
    HAVING COUNT(*)>1) AS S2
    ON S1.emailaddr = S2.emailaddr
    WHERE emailid <> min_emailid
    You will also want to put a unique constraint on emailaddr once you've cleaned up the table
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    2005 air code:
    Code:
    SELECT emailid
    FROM   (
            SELECT emailid
                 , emailaddr
                 , Row_Number() OVER (PARTITION BY emailaddr ORDER BY emailid ASC) As [n]
            FROM   cl_email_temp
           ) As [a_subquery]
    WHERE  n <> 1
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2006
    Posts
    87
    hi will this work inserting the delete statement before it
    DELETE
    FROM
    (SELECT S1.*
    FROM cl_email_temp AS S1
    JOIN
    (SELECT emailaddr, min_emailid = MIN(emailid)
    FROM cl_email_temp
    GROUP BY emailaddr
    HAVING COUNT(*)>1) AS S2
    ON S1.emailaddr = S2.emailaddr
    WHERE emailid <> min_emailid)

    THANKS

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Firstly, check the select works.
    Secondly, test this on a dev\ test box.
    Finally, just change the SELECT S1.* to DELETE S1 (in my posted code, not yours)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    2005 air code:
    Code:
    SELECT emailid
    FROM   (
            SELECT emailid
                 , emailaddr
                 , Row_Number() OVER (PARTITION BY emailaddr ORDER BY emailid ASC) As [n]
            FROM   cl_email_temp
           ) As [a_subquery]
    WHERE  n <> 1
    George - you really <3 SQL Server 2005 don't you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    George - you really <3 SQL Server 2005 don't you?
    I have no idea what yo're on about Mr Flump
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2006
    Posts
    87
    No I don't have mssql 2005 but 2000 so the solution of george doesn't work because of the row_number function...

    The select statement of pootle flump works fine so as suggested substituted the SELECT with delete but it gives me syntax error i posted the error below



    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '*'.
    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'AS'.


    DELETE S1.*
    FROM cl_email_temp AS S1
    JOIN
    (SELECT emailaddr, min_emailid = MIN(emailid)
    FROM cl_email_temp
    GROUP BY emailaddr
    HAVING COUNT(*)>1) AS S2
    ON S1.emailaddr = S2.emailaddr
    WHERE emailid <> min_emailid

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hee hee - read my post #5 carefully and compare to your new code. You didn't quite apply what I said! (I'll help you with the bug but always worth looking at it yourself first). Here's a clue - there's a clue in the error message!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    http://doc.ddart.net/mssql/sql70/de-dz_5.htm

    DELETE FROM <table_name>
    (i.e. drop the S1.*)
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    DELETE FROM <table_name>
    (i.e. drop the S1.*)
    You checked that lil' buddy? I haven't tested but I think that's a wrong 'un.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    D'oh.
    It's because of the alias and referencing the same table twice, right?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The alias doesn't cause a problem, but two+ tables in the FROM clause does (whether or not they are the same physical table)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Now I've written the query myself it makes sense
    I can only air-code so far, it seems.

    George
    Home | Blog

  15. #15
    Join Date
    Aug 2006
    Posts
    87
    couldn't figure our really sorry for that.... could you please help me more with it it gives me all the same error..

Posting Permissions

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