Delete Duplicate values

,

Example.

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

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

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

BEGIN
	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
END

SELECT * FROM #tmpDuplicateVals

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

Rate

2.71 (7)

Share

Share

Rate

2.71 (7)