How to Delete Duplicate Rows in SQL?

In this section, we learn different ways to delete duplicate rows in MySQL and Oracle. If the SQL table contains duplicate rows, then we have to remove the duplicate rows.

Preparing sample data

The script creates the table named contacts.

In the above table, we have inserted the following data.

We execute the script to recreate test data after executing a DELETE statement.

The query returns data from the contacts table:

The following SQL query returns the duplicate emails from the contact table:

We have three rows with duplicate emails.

(A) Delete duplicate rows with the DELETE JOIN statement

Output:

Three rows had been deleted. We execute the query, given below to finds the duplicate emails from the table.

The query returns the empty set. To verify the data from the contacts table, execute the following SQL query:

The rows id's 9, 12, and 14 have been deleted. We use the below statement to delete the duplicate rows:

Execute the script for creating the contact.

(B) Delete duplicate rows using an intermediate table

To delete a duplicate row by using the intermediate table, follow the steps given below:

Step 1. Create a new table structure, same as the real table:

Step 2. Insert the distinct rows from the original schedule of the database:

Step 3. Drop the original table and rename the immediate table to the original one.

For example, the following statements delete the rows with duplicate emails from the contacts table:

(C) Delete duplicate rows using the ROW_NUMBER() Function

Note: The ROW_NUMBER() function has been supported since MySQL version 8.02, so we should check our MySQL version before using the function.

The following statement uses the ROW_NUMBER () to assign a sequential integer to every row. If the email is duplicate, the row will higher than one.

The following SQL query returns id list of the duplicate rows:

Output:

Delete Duplicate Records in Oracle

When we found the duplicate records in the table, we had to delete the unwanted copies to keep our data clean and unique. If a table has duplicate rows, we can delete it by using the DELETE statement.

In the case, we have a column, which is not the part of group used to evaluate the duplicate records in the table.

Consider the table given below:

Suppose, we want to keep the row with the highest VEGETABLE_ID and delete all other copies.

We use the DELETE statement to delete the rows whose values in the VEGETABLE_ID COLUMN are not the highest.

Three rows have been deleted.

If we want to keep the row with the lowest id, use the MIN() function instead of the MAX() function.

The above method works if we have a column that is not part of the group for evaluating duplicate. If all values in the columns have copies, then we cannot use the VEGETABLE_ID column.

Let's drop and create the vegetable table with a new structure.

In the vegetable table, the values in all columns VEGETABLE_ID, VEGETABLE_NAME, and color have been copied.

We can use the rowid, a locator that specifies where Oracle stores the row. Because the rowid is unique so that we can use it to remove the duplicates rows.

The query verifies the deletion operation: