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
x
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 )