How to delete duplicate rows from Oracle table

December 18, 2017 techgoeasy Comment

Last updated on December 22nd, 2017 at 06:14 pm

We often need to find and delete duplicate rows from oracle table due to many reasons in the database. We need to delete to clear off the data issues often. There are many ways to oracle delete duplicate rows but keep original .I would be showing off few faster method to achieve it in this post. Please note that you must identify all the column that make the row a duplicate in the table and specify all those columns in the appropriate delete statement in SQL

Here are some of the ways to delete duplicate rows in an easy manner

A) Fast method but you need to recreate all indexes,triggers

create table my_table1 as select distinct * from my_table;
drop my_table;
rename my_table1 to my_table;

B) How to find and delete duplicate records in oracle using rowid. The below example shows one column.if you have delete based on two column ,you can specify two column

Delete from my_table where rowid not in (
select max(rowid) from my_table group by my_col_name );

C) Use self-join to delete duplicate rows

DELETE FROM my_table A WHERE ROWID > (SELECT min(rowid) FROM my_table B WHERE A.key_values = B.key_values);

D) Use exists clause

delete from my_table t1
where exists (select ‘x’ from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid);

E) delete duplicate records in oracle using analytical function

delete from my_table
where rowid in
(select rid
from
( select
rowid rid,
row_number() over (partition by column_name order by rowid) rn
from my_table)
where rn <> 1
)

So as shown,there are many ways to delete duplicate rows in the tables.These command could handy in many situation and can be used depending on the requirement.Please always make sure we have backup available before executing any statements. We should also first find the duplicate using the query and then verify it before committing it

How to find the duplicate rows without deleting it

select * from my_table
where rowid not in
(select max(rowid) from my_table group by column_name);

So First find the duplicate using above query, then delete it and deletion count should be same as row count of query above.Now run the find duplicate query again.If no duplicate then we are good for commit

Please look at below article to have deep dive in Sql

Sql and PLsql useful tutorials

Leave a Comment Cancel Reply