Delete duplicate rows from Oracle

Author

shabbir ( Go4Expert Founder )

Shabbir is a developer in the field of Applications, web as well as database designing and is devoted to the optimization and usability of the code. He maintains Programming forum and is a C++ addict.

All articles By shabbir

Recent Articles

• Oracle Nested Tables and Varrays, Started by kidas in Oracle
• Avoiding TOO_MANY_ROWS Error In Oracle, Started by pradeep in Oracle
• Datatypes in Oracle, Started by Sanskruti in Oracle
• SQL*Plus, Started by Sanskruti in Oracle
• Exception Handling, Started by Sanskruti in Oracle

Similar Articles

• Audit Trail in Oracle, Started by shabbir in Oracle
• Uninstalling Oracle 8 Personal Database, Started by shabbir in Oracle

There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully.

Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID

CREATE TABLE duptest
  ( Id varchar(5),
  nonid varchar(5));

I create a table with just 2 fields. I dont have any constraint on ID because I would import some data which may be duplicated but after importing I would like to make the ID as PRIMARY KEY

Insert some date to simulate duplicates

INSERT INTO duptest VALUES('1','a');

Insert them more than once.

INSERT INTO duptest VALUES('2','b');

SQL to delete the duplicate rows

DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);

Explanation :- In the subquery I Select a unique rowid from all the rowid's It can be MAX, MIN any one and the group by clause should include all the UNIQUE columns I desire. Say I want a composite primary key then the group by should be group by id1,id2 ...

The the subquery returns one record for the dupliacte ID's and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.

[coderzone]

Thats good way to delete the rows.

[lead.smart34]

nice way

[harminder]

a way to delete duplicate rows in oracle table You can also detect and delete duplicate rows using Oracle analytic functions:

delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);

[kidas]

Delete Duplicate Rows using Analytic functions

DELETE FROM TABLENAME
WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY dup_col_1) rnk
                              	   FROM   TABLENAME)
                WHERE rnk>1);

[Alok_1978]

Incase if you want to check for all the columns then try this query:

delete from emp where rowid not in (select max(rowid) from emp group by id,name,no)

My emp table had 3 columns id,name,no and you need to group by all the columns in order to check if any column contains different value or not.if yes then that record will not be considered as duplicate and will not be deleted. The above query will delete the record only when all the columns have same value