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 )