Delete duplicate values from table w/o Primary Key example


Let’s do some data cleaning work. This always happens when the database is not properly designed as per the normalization rules; the user has to deal with non-unique values in the table. And later on remove those non-unique values keeping the one. There are different ways to write the script below example will demonstrate you how you can achieve this.

IF OBJECT_ID('tempdb..#tmpDuplicateVals') IS NOT NULL
      DROP TABLE #tmpDuplicateVals
GO

CREATE TABLE #tmpDuplicateVals(
      Col1 VARCHAR(3),
      Col2 VARCHAR(3),
      Col3 VARCHAR(3)
)
GO 

----Populate some values
INSERT INTO #tmpDuplicateVals VALUES('aa1','aa1','aa1')
INSERT INTO #tmpDuplicateVals VALUES('aa1','aa1','aa1')
INSERT INTO #tmpDuplicateVals VALUES('aa2','aa2','aa2')
INSERT INTO #tmpDuplicateVals VALUES('aa2','aa2','aa2')
INSERT INTO #tmpDuplicateVals VALUES('aa3','aa3','aa3')
INSERT INTO #tmpDuplicateVals VALUES('aa3','aa3','aa3')
INSERT INTO #tmpDuplicateVals VALUES('aa4','aa4','aa4')
INSERT INTO #tmpDuplicateVals VALUES('aa4','aa4','aa4')
INSERT INTO #tmpDuplicateVals VALUES('aa5','aa5','aa5')
INSERT INTO #tmpDuplicateVals VALUES('aa5','aa5','aa5') 

SELECT * FROM #tmpDuplicateVals; 

---Removes the duplicate records
;WITH cteDV( RID, Col1, Col2, Col3 ) AS
(SELECT ROW_NUMBER() OVER ( PARTITION BY Col1, Col2, Col3 ORDER BY Col1, Col2, Col3 ) RID, * FROM  #tmpDuplicateVals)
DELETE FROM cteDV WHERE RID = 1;

SELECT * FROM #tmpDuplicateVals 

IF OBJECT_ID('tempdb..#tmpDuplicateVals') IS NOT NULL
      DROP TABLE #tmpDuplicateVals
GO
Advertisements

One thought on “Delete duplicate values from table w/o Primary Key example

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s