SQL Deleting Duplicate Rows

by ebarcza 4/5/2012 5:35:00 AM

Here is a SQL Example for deleting duplicate rows in a database.

  1. find them using appropriate group by and having clauses
    Select OperatorID, LocationID, ProdMonth, ProdYear, Count(*) as cnt   from CrudeDeals 
    where ProdMonth = 3 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1
  2. Isolate the first PKID of each of the duplicate Records sets
    Select MIN(CrudeDealID),OperatorID, LocationID, ProdMonth, ProdYear, Count(*) as cnt   from CrudeDeals 
    where ProdMonth = 3 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1
  3. Now Delete the first record of each of the duplicate sets
    Delete CrudeDeals where CrudeDealID in(
    Select MIN(CrudeDealID)  from CrudeDeals 
    where ProdMonth = 5 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1)

    BOOM! no more dups
Note that the first two SQL statement are really just affirming the data sets that you are about to delete. All the work could be done with Step 3 if you are cocky enough in your coding ability to pull the trigger without first inpsecting what records it will delete. So if you've got the balls, skip right to step 3!

Tags:

SQL Server Reporting Services

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  July 2018  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2018

Sign in