SQL Interview Questions | Remove duplicate entries
Question
| empid | empname | managerid | deptid | Salary |
| 1 | Emp1 | 0 | 1 | 6000 |
| 2 | Emp2 | 0 | 5 | 6000 |
| 3 | Emp3 | 1 | 1 | 2000 |
| 13 | Emp13 | 2 | 5 | 2000 |
| 11 | Emp11 | 2 | 1 | 2000 |
| 9 | Emp9 | 1 | 5 | 3000 |
| 8 | Emp8 | 3 | 1 | 3500 |
| 7 | Emp7 | 2 | 5 | NULL |
| 3 | Emp3 | 1 | 1 | 2000 |
In the same table, some duplicate records might be present by mistake. Sort out a way to locate them and find a way to delete them.
in progress
2
SQL
4 years
4 Answers
1515 views
Great Grand Master 0
Answers ( 4 )
WITH cte as (
Select *,Row_number() OVER (PARTITION BY
empid,empname,managerid,deptid,salary
ORDER BY
empid,empname,managerid,deptid,salary) row_num
FROM Employees)
Delete from cte
where row_num > 1
Delete empid from employees e1 where empid
(select max(empid) from employees e2 where e1.empid = e2.empid);
Delete empid from employees e1 where empid
(select max(empid) from employees e2 where e1.empid=e2.empid);
Delete from Employees
where empid in
(select empid, count(*)
from Employees
group by empid having count(*)>1
)