Skip to content
Advertisement

Deleting duplicates rows from oracle

I am using oracle database.I want to use duplicate rows from a table except one,which means that I want to delete all rows but atleast one row should be there. I have a table

employee_id ---- department_id
1                     10
2                     10
1                     20
3                     30
2                     30

Now i want to delete duplicate rows but at least one row should be there.

select count(employee_id),employee_id 
from employee
group by employee_id
having count(employee_id) >1));

i had used this to find number of employees that are in more than one department but could not find a way to move further. If i use a delete there it will delete all duplicates,But i want to keep one copy.

delete from empl where eno IN( select eno from( select count(eno),eno from empl group by eno having count(eno) >1));

EDIT:I want to retain employee_id Any one who can guide me further

Advertisement

Answer

delete from employee a
where employee_id in (
   select employee_id 
   from employee b
   where b.department_id > a.department_id )
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement