How to delete duplicate rows from a table



Last updated on September 1st, 2016 at 04:22 am

We often find duplicate rows in the table due to many reasons in the database. We need to delete to clear off the data issues often. There are many days to to delete duplicate rows from a table.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

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) Here we have to delete duplicate rows based on the column. 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);

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.

Please look at below article to  have deep dive in Sql

Oracle sql and plsql

Oracle sql interview questions

Oracle sql tutorial :Restricting the data set

Single row functions in sql

how to write sql queries


Leave a Reply