How to Delete Duplicate Rows in Oracle

In this oracle tutorial, we will learn how to delete duplicate rows in oracle database 21c. Moreover, we will take multiple examples where we need to delete duplicate rows in the oracle database.

A table holds duplicate rows when the two or more rows in the tables are identical and the values of the bother rows are equated. However, duplicate data can cause issues in the database. So, here we will understand how to delete duplicate rows in oracle.

Here we will discuss:

• How to delete duplicate rows in oracle
• How to delete duplicate rows in oracle using rownum
• How to delete duplicate rows in oracle using row_number
• How to delete duplicate rows in oracle without using rowid
• How to delete duplicate rows in oracle table

How to delete duplicate rows in oracle

The duplicate rows are defined as two or more identical or the same rows present inside the table.

Here we will delete the duplicate records from the table in oracle. We create a table and inserted the values inside the table. Here is the table for our example and this is the employees table which is located in the Pdb database.

Once the table is created successfully you can see that there are duplicate rows in the table and we want to delete unfavorable copies of data to keep our data clean. These are the duplicate records that we want to delete.

For deleting the duplicate rows first we search all the distinct records from the table. The duplicates are the records, all the data are the same except the row_id because the record_id is the physical address of the records.

Now we will find the distinct rows which have no duplicates rows by using the below query

SELECT * FROM EMPLOYEES
WHERE rowid IN 
( SELECT MAX(rowid)  
FROM EMPLOYEES
GROUP BY EMP_ID, EMP_NAME,EMP_ADDRESS,EMP_DESIGNATION,DEPT_ID);

This is how we find the distinct rows with maximum rowid without any duplicate rows.

Now we want to delete the duplicate rows with their rowid by simply using the delete statement with a subquery. For deleting duplicate rows we use the below query.

DELETE EMPLOYEES  
WHERE rowid NOT IN 
  (SELECT MAX(rowid)  
FROM EMPLOYEES
GROUP BY EMP_ID, EMP_NAME,EMP_ADDRESS,EMP_DESIGNATION,DEPT_ID);

SELECT * FROM EMPLOYEES;

This is how we can delete the duplicate rows, after deleting the duplicate rows the output would be the same as the distinct records. We deleted all the unfavorable copies of data from the table in one go.

Read: How to convert rows into columns in Oracle

How to delete duplicate rows in oracle using rownum

Rownum is defined as a numeric sequence number. It is a non-permanent allotted sequence to the row and permits recovering a row holding data. The rownum can be used for allocated unique values for each row in the table.

It is a dynamic value that is recovered automatically with the particular statement outputs and it also contains the serial number of the rows.

From deleting the duplicate rows in oracle using rownum, create a table and inserted the values inside the table. Here is the table for our example and this is the customer’s table which is located in the Pdb database.

Once the table is created successfully you can see that there are duplicate rows in the table and we want to delete these duplicate rows.

Now we want to delete the duplicate rows using rownum by simply using the delete statement with a subquery. For deleting duplicate rows we use the below query.

delete from CUSTOMERS a where (CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS) in (
select CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS from CUSTOMERS a group by CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS having count(1) > 1)
and rownum < (select count(1)
from CUSTOMERS c
where c.CUSTOMER_ID = a.CUSTOMER_ID
and c.CUSTOMER_NAME=a.CUSTOMER_NAME
and c.CUSTOMER_ADDRESS=a.CUSTOMER_ADDRESS)

SELECT * FROM CUSTOMERS;

This is how we can delete the duplicate rows, after deleting the duplicate rows the output would be seen as neat and clean, and all the unfavorable copies of data from the table would be deleted in one go.

Read: Oracle Create Index

How to delete duplicate rows in oracle using row_number

Here we are deleting the duplicate rows in oracle using row_number. Row_number id is defined as a function that is used to give a successive number of rows. It can also allow the unique sequential number to every row in the result set.

It can also define as a temporary value that is computed when we run our query.

From deleting the duplicate rows in oracle using row_number, create a table and inserted the values inside the table. Here is the table for our example and this is the Suppliers table which is located in the Pdb database.

Once the table is created successfully you can see that there are duplicate rows in the table and we want to delete these duplicate rows.

select count(*)
from   ( select SUPPLIER_ID
         ,      row_number() over ( partition by SUPPLIER_NAME
                                    order by SUPPLIER_ID) rn
         from   SUPPLIERS
       )
where  rn>1

Now we want to delete the duplicate rows using row_number by simply using the delete statement with a subquery. For deleting duplicate rows we use the below query.

delete from SUPPLIERS
where  rowid in
( select rwid
  from ( select rowid rwid
         ,      row_number() over ( partition by SUPPLIER_NAME
                                    order by SUPPLIER_ID) rn
         from   SUPPLIERS
       )
  where  rn>1
)

SELECT * FROM SUPPLIERS;

This is how we can delete the duplicate rows, after deleting the duplicate rows in oracle using row_number

Read: Alter Table Add Column in Oracle

How to delete duplicate rows in oracle without using rowid

Here we are deleting the duplicate rows in the oracle without using rowid. Firstly created a temporary table where we placed our duplicate rows. The EMP_TEMP table is our temporary table. We also created a table EMPLOYEES in which all the rows present duplicates and non-duplicates. For creating the temporary table we use the below query.

Create Table EMP_TEMP as
Select *
From EMPLOYEES
Group By EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DESIGNATION,DEPT_ID
Having count(*) > 1;
SELECT * FROM EMP_TEMP;

In the above output, we can see that the temporary table is created and the duplicate rows are shown that are placed inside the EMP_TEMP table By using the Select * From EMP_TEMP; query, there are two duplicate rows and these two duplicate rows are placed inside the EMP_TEMP table.

After creating the temporary table in which the duplicated values are present now we want to delete these duplicate values. For deleting the duplicate rows without using rowid we use the below query.

Delete From EMPLOYEES
where (EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DESIGNATION,DEPT_ID) in
(Select EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DESIGNATION,DEPT_ID
From EMP_TEMP);
Select * From EMPLOYEES;

This is how we can delete the duplicate rows without using rowid, after deleting the duplicate rows the output would be seen in which only two rows are remaining which are not duplicates and all the other rows are deleted from the table because of their duplicity.

Read: How to copy a table in Oracle

How to delete duplicate rows in the oracle table

Here we are deleting duplicate rows in the oracle table. We create a table and inserted the values inside the table. Here is the table for our example and this is the customer table which is located in the Pdb database.

Once the customer table is created successfully you can see that there are duplicate rows in the table and we want to delete unfavorable copies of data to keep our data clean. These are the duplicate records that we want to delete.

Now we want to delete the duplicate rows with their rowid by simply using the delete statement with a subquery. For deleting duplicate rows we use the below query.

DELETE FROM CUSTOMER
WHERE rowid not in
(SELECT MIN(rowid)
FROM CUSTOMER
GROUP BY CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS);

SELECT * FROM CUSTOMER;

This is how we can delete the duplicate rows in the oracle table, after deleting the duplicate rows the output would be seen as neat and clean and no duplicity has shown in the oracle table.

You may also like to read the following Oracle tutorials.

• How to Check Oracle Database Version
• How to create a user in Oracle
• How to get database name in Oracle

In this article, we have learned how to delete duplicate rows in oracle. And also, we have covered these topics.

• How to delete duplicate rows in oracle
• How to delete duplicate rows in oracle using rownum
• How to delete duplicate rows in oracle using row_number
• How to delete duplicate rows in oracle without using rowid
• How to delete duplicate rows in oracle table