How To Delete Duplicates In Oracle

| Posted on by



Summary: in this tutorial, you will learn how to find duplicate records in the Oracle Database.

Luckily Oracle Database has a couple of other tricks available. Delete All Rows in a Partition Fast. When you partition a table, you logically split it into many sub-tables. You can then do operations which only affect rows in a single partition. This gives an easy, fast way to remove all the rows in a partition. Drop or truncate it! To remove duplicate rows of data, use the following statement: DELETE FROM mytable WHERE rowid NOT IN ( SELECT MAX(ROWID) FROM mytable GROUP BY colA,colB,colC ); In the GROUP BY clause, enumerate all of your columns in your table, or the columns you think should be the primary key columns.

Let’s start by setting up a sample table for the demonstration.

Setting up a sample table

First, the following statement creates a new table named fruits that consists of three columns: fruit id, fruit name, and color:

Second, insert some rows into the fruits table:

Third, query data from the fruits table:

As you can see from the picture above, the fruits table has duplicate records with the same information repeated in both fruit_name and color columns.

Finding duplicate rows using the aggregate function

To find duplicate rows from the fruits table, you first list the fruit name and color columns in both SELECT and GROUP BY clauses. Then you count the number of appearances each combination appears with the COUNT(*) function as shown below:

The query returned a single row for each combination of fruit name and color. It also included the rows without duplicates.

To return just the duplicate rows whose COUNT(*) is greater than one, you add a HAVING clause as follows:

So now we have duplicated record. It shows one row for each copy.

How To Remove Duplicates In Oracle

If you want to return all the rows, you need to query the table again as shown below:

FilesDuplicates

Now, we have all duplicate rows displayed in the result set.

Finding duplicate records using analytic function

See the following query:

In this query, we added an OVER() clause after the COUNT(*) and placed a list of columns, which we checked for duplicate values, after a partition by clause. The partition by clause split rows into groups.

Oracle Delete Sql

Different from using the GROUP BY above, the analytic function preserves the result set, therefore, you still can see all the rows in the table once.

How To Remove Duplicates In Oracle Sql Developer

Because you can use the analytic function in the WHERE or HAVING clause, you need to use the WITH clause:

How To Delete Duplicates In Oracle Sql

Or you need to use an inline view:

How To Remove Duplicates In Oracle From Select

Now, you should know how to how to find duplicate records in Oracle Database. It’s time to clean up your data by removing the duplicate records.