Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Delete duplicate records in SQL database using stored procedure

Posted on 2007-12-04
Medium Priority
Last Modified: 2008-09-11
I have a SQL server 2000 database that has over a million records in it.  I need to create a stored procedure that i can run that we check for which records have duplicates and then delete all of the duplicates except for one.  The criteria for the duplicates is based on five different columns.  I am hoping somebody can give me some guideance on how to do this.  
Question by:Mike_Stevens
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20405242
Can you provide the structure of the table? Is there a unique key?

Author Comment

ID: 20405276

Column: RecordNumber -  Unique Key
Column: IDNum
Column: Code
Column: Date
Column: Time
Column: State

All of the columns except RecordNumber need to be used in criteria.  Sample below

RecordNumber  IDNum  Code  Date         Time   State
------------------  ---------  ------  -------        ------   --------
100                   12345     A     12122007  1630    CA
101                   12345     A     12122007  1630    CA
102                   12345     A     12122007  1630    CA
103                   12345     A     12122007  1630    CA
104                   12345     A     12122007  1630    CA

Using the sample data above, procedure needs to delete all but one of the records.


Expert Comment

ID: 20405356
I would create a new table with the unique index on those fields, export the data from your old table into this new one (which will only take one of each duplicate because of the index), then clear out your old table and move the records from your new table back to the old table.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 17

Accepted Solution

Daniel Reynolds earned 2000 total points
ID: 20405880
Here is some code you can run iteratively until the rows affected = 0

DELETE  dbo.tblStatus
--SELECT top 1 * 
FROM dbo.tblStatus
WHERE Exists (Select Count(*), IDNum, Code, [Date], [Time], [State]
                              from dbo.tblStatus
                             Group By IDNum, Code, [Date], [Time], [State]
                             Having Count(*) > 1)

Open in new window

LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20405903
And here is a link with additional information.
and one that shows how to do it with a little bit more programming, but all at once.

LVL 11

Expert Comment

ID: 20408127
Code provided by xDJR1875: will delete all record which have duplicate data but Mike_Stevens intended to leave a row for the duplicate data.

You can try as below, it will only left the max record number for the duplicates data.

DELETE  FROM tblStatus
        SELECT MaxRecordNumber, IDNum, Code, Date, Time, State
              SELECT Max(RecordNumber) AS MaxRecordNumber, IDNum, Code, Date, Time, State
              FROM  dbo.tblStatus
              GROUP BY IDNum, Code, Date, Time, State
          ) A
         WHERE A.MaxRecordNumber = tblStatus.RecordNumber AND
                     A.IDNum = tblStatus.IDNum AND A.Code = tblStatus.Code AND
                     A.Date = tblStatus.Date AND A.Time = tblStatus.Time AND
                     A.State = tblStatus.State
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20408152
Actually, with the SET ROWCOUNT = 1
The code does not delete all duplicate records.
LVL 11

Expert Comment

ID: 20408626
Sorry, my mistake, but for the example of data, the duplicate rows can be more than 2, set rowcount =1  will only delete one of the records which having duplicate.
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20409336
You are correct that is why I suggest Mike run it iteratively until rows affected = 0.

It is not the most elegant of solutions, but maybe one of  the simplest.

Expert Comment

ID: 21502162
    FROM    MyTable
        FROM        MyTable
        GROUP BY    DuplicatevalueColumn1, DuplicateValueColumn2,

Reference:Pinal Dave

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question