Finding and deleting duplicate rows from a table

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);  

Reader Feedback

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.