Sunday, June 7, 2015

How to remove duplicate entry from SQL Table?

CaseProblem table has duplicate entry for CaseDetailId 
Id  CasedetailId
1    20152
2    20152 
3    20153
WITH CTE AS(
   SELECT CaseDetailId,
       RN = ROW_NUMBER()OVER(PARTITION BY CaseDetailId ORDER BY CaseDetailId)
   FROM dbo.CaseProblem
)
--select * FROM CTE WHERE  RN > 1   
 Delete  * FROM CTE WHERE  RN > 1

Final Result 
Id  CasedetailId
1    20152
3    20153
2    20152 this record should be delete.

No comments: