5 ways to delete duplicate records Oracle

In Oracle there are many ways to delete duplicate records. Note that below example are described to just explain the different possibilities.

Consider the EMP table with below rows

create table emp(
EMPNNO  integer,
EMPNAME varchar2(20),
SALARY  number);

10    Bill    2000
11    Bill    2000
12    Mark    3000
12    Mark    3000
12    Mark    3000
13    Tom    4000
14    Tom    5000
15    Susan    5000

1. Using rowid

SQL > delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

This technique can be applied to almost scenarios. Group by operation should be on the columns which identify the duplicates.

2. Using self-join

SQL > delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );

3. Using row_number()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);

This is another efficient way to delete duplicates

4. Using dense_rank()

SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);

Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.

5. Using group by

Consider the EMP table with below rows

10    Bill    2000
11    Bill    2000
12    Mark    3000
13    Mark    3000

SQL > delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);

This technique is only applicable in few scenarios.

Always take extra caution while deleting records. 

1. First identify the duplicates using select.

2. Double verify those are actual  ‘duplicates’ or not

3. Take backup if necessary

4. Apply commit only if you are sure.

Did you find above post useful ? Your comments are highly valuable.

68 responses to “5 ways to delete duplicate records Oracle”

  1. Thanks. It’s very usefull.

    1. super.u r highly talented…………………..

      1. That’s funny. Anyway thanks

      2. Very very thanks it’s very usefull.

  2. I constantly sρent my hallf an hoսг to read this website’s content all thhe time
    alߋng with a cup of coffee.

  3. very useful thanks a lot…………..

      1. can you write a plsql code to remove duplicate records in a table and those removed will be copied to new table.
        post answer here and to my mail pradeepgssmg@gmail.com

  4. Thank you, very useful function “partition”.

  5. All are easiest methods.. thanks a lot… I got the better result when I use the 1st Method.. Thanks again.

  6. Thanks bro… u r really great

  7. delete from xyz where rowid not in(
    select max(rowid) from xyz group by empno)

  8. is it correct way to eliminate duplicates.,, I tried for elimination of complete row duplicate in a table

  9. Yeah! it is very usefull session for me…! thank you….!!!

  10. Why cant we use rownum in place of rowid in the very first solution .

  11. That’s help full for me and other learner…..

  12. Its really usefull
    thanks 🙂

  13. Can u plz explain the self join method of removing duplicate rows from a table.

  14. Can u explain self join method of removing duplicate values

  15. CAN U PLS SHARE,, how to delete duplicate rows from table in oracle 8i

    1. All most all above will eork in 8i too

  16. Good work. Keep going.

  17. when i delete using rowid it was not working….

  18. HI, by reading this, i have cleared my confson whcih s torchering since last i.5 years

  19. Thanks it is very useful can we have more queries like this..?

  20. Excellent.. very much useful

  21. Delete from employee a where ROWID >
    (select min(ROWID) from employee b where a,eid=b,eid);

    I think it is the most easy way to delete the newer records from table. 🙂
    #stayhappykeepcoding

  22. The last method using group by seems to be incorrect. It will delete all duplicate occurring rows instead of deleting all but one.

  23. it’s really helpful. Appreciated for your effort.

  24. Without using row id how to delete duplicate data from a table? Can anyone help me?

  25. thank u sir…………..it helps me verymuch……

  26. Excellent.. very much useful

  27. these queries doesnt run in sql server,,,what to do?,plz anyone help me out

  28. Wow!Perfect!Thanks for sharing

  29. Hi,
    By using first method total records are deleted not only the duplicate records can you please tell me the reason
    delete from piuswhere rowid not in (select min(rowid) from piusgorup by ename);
    here pius is tablename and ename is duplicate column

  30. how can we delete 2nd duplicate from a table by rowid

  31. 1) Using rowid it will not delete the duplicate entries

  32. I want do delete duplicate records but with one entry / records available.

  33. Mind blowing and blasting approach….thanks u so much.

  34. My table has two records that have the same data (duplicate). It has no primary key, no AutoIncrement column, no ROWID column (almost every example I find talks about ROWID, but when I use it on a query it says error: “ROWID column not found”). How do I proceed in this case?

    1. 4. Using dense_rank()

      SQL > delete from emp where rowid in
      (
      select rid from
      (
      select rowid rid,
      dense_rank() over(partition by empno order by rowid) rn
      from emp
      )
      where rn > 1
      );

  35. Highly helpful…Thanks for the post..

  36. thanks very useufull

  37. Explanation is very useful and perfect with examples. Good work , Keep going and thanks. 🙂

  38. thank you for your information…

  39. /*Using New Table*/
    create table EMP2 as
    select distinct * from EMP;
    drop table EMP;
    alter table EMP2 rename to EMP;

Leave a Reply to ashok Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: