How to delete duplicate records from a table in sql
|
Smith
2936
Points
79
Posts
|
I have Emp table with duplicate record. I want a single command to remove duplicate record that is, keep one record from the duplicates.
Views:
8732
Total Answered: 2 Total Marked As Answer: 1 Posted On: 23-Nov-2015 23:03 Share: 
|
Answers
|
NiceOne...
1390
Points
18
Posts
|
hi smith, You can use ROW_NUMBER() with PARTITION BY to delete duplicate. Suppose you have emp table with duplicate records as:
and want emp after deletion of duplicate records as:
Use following CTE Query to delete duplicate record from the table emp: WITH tmp AS(SELECT name,salary,ROW_NUMBER() OVER(PARTITION BY name,salary ORDER BY name) AS duplicateCount FROM emp)
DELETE FROM tmp WHERE tmp.duplicateCount>1
Now execute select query again SELECT Name, salary
FROM Emp
result will be as:
Posted On:
20-Dec-2015 00:11
|
||||||||||||||||||||
|
Smith
2936
Points
79
Posts
|
Thanks... Finally I got the solution.
Posted On:
26-Dec-2015 01:06
|
Blog
Active User (27)
