Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Duplicate Records

    Hi All,

    I have identified duplicate records that I need to delete. I am not sure how to do that. Any suggestions would be greatly appreciated.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could select a unique set of the duplicates to a temp table, delete all of the duplicates from the permanent table, then reinsert the rows you saved in the temp table.

    Oh, and impose a primary key immediately after that ;-).

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mcrowley, i will bet you a beer that the table already has a primary key, and that it's an IDENTITY column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2004
    Posts
    268
    What about primary key? How will I be able to reinsert it? I can't select the primary key field in my query that returns duplicate rows.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Does your table have a key or not? If it does then it doesn't have duplicates. This example may help:

    Code:
    CREATE TABLE tbl (col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL /* NO KEY!! */);
    INSERT INTO tbl VALUES (1,1,1);
    INSERT INTO tbl VALUES (1,1,1); -- duplicate
    INSERT INTO tbl VALUES (2,2,2);
    INSERT INTO tbl VALUES (2,2,2); -- duplicate
    
    WITH t(r) AS
     (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY col1, col2, col3)
      FROM tbl)
     DELETE t WHERE r > 1;

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds like I owe you a beer, Rudy.

    Inka: how are you defining "duplicates".

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    Does your table have a key or not? If it does then it doesn't have duplicates.
    that is mere pedantry, david

    with an IDENTITY primary key, it most certainly is possibly to have "duplicates" -- duplicates of the "real" key, which invariably was not defined with a UNIQUE constraint

    consider --

    123 tom
    125 rick
    126 harry
    127 tom
    129 david

    see the duplicate?

    do a search in your favourite search engine for "removing duplicates" and you will see that there are literally millions of web pages on this topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    inka:

    How do you decide a row is a "duplicate" for you? When you have "duplicate" rows, how do you decide which one of them you want to keep?

    This kind of problem usually isn't hard to fix, and once fixed it is usually easy to prevent it from re-occuring. The problem is that every time I see this kind of problem, the definition of "duplicate" and the way to determine which row you want to keep seems to be unique!

    -PatP

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937
    that is mere pedantry, david

    with an IDENTITY primary key, it most certainly is possibly to have "duplicates" -- duplicates of the "real" key, which invariably was not defined with a UNIQUE constraint

    consider --

    123 tom
    125 rick
    126 harry
    127 tom
    129 david

    see the duplicate?

    do a search in your favourite search engine for "removing duplicates" and you will see that there are literally millions of web pages on this topic
    Exactly. But unfortunately these questions usually begin like inka's did with someone saying "I need to delete duplicates" and believing they have adequately specified the problem. Knowing what keys exist is a pre-requisite to solving almost any problem in SQL, yet that information is very commonly left out.

  10. #10
    Join Date
    Jul 2004
    Posts
    268
    dportas,

    Thank you very much for your solution. It gave expected results.

Posting Permissions

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