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.
sqlandplsql.com - Learn SQL and DATABASES 
68 responses to “5 ways to delete duplicate records Oracle”
Thanks. It’s very usefull.
super.u r highly talented…………………..
Very very thanks it’s very usefull.
That’s funny. Anyway thanks
super.u r highly talented…………………..
Very very thanks it’s very usefull.
That’s funny. Anyway thanks
That’s funny. Anyway thanks
I constantly sρent my hallf an hoսг to read this website’s content all thhe time
alߋng with a cup of coffee.
Thanks
Thanks
very useful thanks a lot…………..
You are welcome.
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
You are welcome.
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
Thank you, very useful function “partition”.
Very very thanks it’s very usefull.
All are easiest methods.. thanks a lot… I got the better result when I use the 1st Method.. Thanks again.
Thanks for the comment
Thanks for the comment
very usefull
Good one
Thanks
Thanks
good job..
Thanks bro… u r really great
thanks
very useful ur text
delete from xyz where rowid not in(
select max(rowid) from xyz group by empno)
is it correct way to eliminate duplicates.,, I tried for elimination of complete row duplicate in a table
Yeah! it is very usefull session for me…! thank you….!!!
Thanks
Thanks
Why cant we use rownum in place of rowid in the very first solution .
That’s help full for me and other learner…..
awesome…thank u
Its really usefull
thanks 🙂
Can u plz explain the self join method of removing duplicate rows from a table.
Can u explain self join method of removing duplicate values
CAN U PLS SHARE,, how to delete duplicate rows from table in oracle 8i
All most all above will eork in 8i too
All most all above will eork in 8i too
Good work. Keep going.
when i delete using rowid it was not working….
HI, by reading this, i have cleared my confson whcih s torchering since last i.5 years
Thanks it is very useful can we have more queries like this..?
Thanks
very useful
Thanks
Thanks
Thanks
Reblogged this on offcampustroubles.
Excellent.. very much useful
Thanks
Thanks
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
The last method using group by seems to be incorrect. It will delete all duplicate occurring rows instead of deleting all but one.
Please explain.
Please explain.
it’s really helpful. Appreciated for your effort.
Good job. Thankyou…..
Without using row id how to delete duplicate data from a table? Can anyone help me?
thank u sir…………..it helps me verymuch……
Excellent.. very much useful
use full information
Check this out:
https://sqlpatterns.wordpress.com/2016/04/22/how-to-delete-duplicate-records-without-using-joins-and-aggregate-functions/
these queries doesnt run in sql server,,,what to do?,plz anyone help me out
Wow!Perfect!Thanks for sharing
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
its really very useful
how can we delete 2nd duplicate from a table by rowid
1) Using rowid it will not delete the duplicate entries
I want do delete duplicate records but with one entry / records available.
Mind blowing and blasting approach….thanks u so much.
Thanks
Thanks
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?
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
);
THANKS
Highly helpful…Thanks for the post..
thanks very useufull
Explanation is very useful and perfect with examples. Good work , Keep going and thanks. 🙂
thank you for your information…
/*Using New Table*/
create table EMP2 as
select distinct * from EMP;
drop table EMP;
alter table EMP2 rename to EMP;
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
);
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