Q
Problem solve Get help with specific problems with your technologies, process and projects.

Deleting a duplicate row in a table

How do you delete a duplicate row in a table?

How do you delete a duplicate row in a table?
You'll have to decide if you want the "first" row or the "last" row. I use those terms loosely as there really is no ordering in a relational table. However, Oracle does include a ROWID psuedo function. The row id can be used to provide an ordering to these rows.
DELETE FROM my_table
WHERE ROWID <> ( SELECT MAX(ROWID)
                 FROM my_table b
                 WHERE b.c1 = my_table.c1
                   AND b.c2 = my_table.c2 );
The join condition can be used to join the table to itself based on your table's columns. The DELETE statement will remove those duplicate rows without the highest ROWID value.
This was last published in September 2005

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close