This tip will show you how to find duplicate rows in a table and then delete them. First, create a table called empl with columns desc and emplid. Now find out the duplicate rows in this table, using the following query:
select desc, emplid from empl group by desc, emplid having count(*) > 1;
To delete duplicate rows from this table, use the following query:
delete from empl a where rowid > ( select min(rowid) from empl b where a.desc = b.desc and a.emplid = b.emplid);
Michael P. writes: The query in this tip works very well as given. But there are times when you also want a count of the duplicates. The original query can be modified slightly to use a subquery, and thus return the count as well.
select count, desc, emplid from ( select count(*) as count, desc, emplid from empl group by desc, emplid ) where count > 1 order by count, emplid;
This is as fast as the original and in a table with lots of duplicates (perhaps from a legacy application or a design change), the count is also nice to know.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, relational model, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in August 2002