Summary: in this tutorial, you will learn various ways to delete duplicate rows in MySQL.

In the previous tutorial, we have shown you how to find duplicate values in a table. Once the duplicates rows are identified, you may want to delete them to clean up your data.

USE UniversityV2 - Finding duplicates using Aggregate method SELECT c.CourseId,c.Name,c.Detail,COUNT(.) AS DuplicateRecords FROM dbo.Course c GROUP BY c.CourseId,c.Name,c.Detail HAVING COUNT(.) 1 Removing Duplicates by Aggregate Method. Let us remove the duplicates using the Aggregate Method as follows. 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! You can use regular expressions and regexpreplace to remove the duplicates after concatenation with listagg: SELECT Num1, RTRIM(REGEXPREPLACE ((listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ), ' (^-.) (-1)+ ($ -)', '13'), '-') Num2s FROM ListAggTest.

Prepare sample data

The following script creates tablecontacts and inserts sample data into the contacts table for the demonstration.

Note that you can execute this script to recreate test data after you execute a DELETE statement.

This query returns data from the contacts table:

The following query returns the duplicate emails in the contacts table:

As you can see, we have four rows with duplicate emails.

A) Delete duplicate rows using DELETE JOIN statement

MySQL provides you with the DELETE JOIN statement that allows you to remove duplicate rows quickly.

The following statement deletes duplicate rows and keeps the highest id:

This query references the contacts table twice, therefore, it uses the table alias t1 and t2.

The output is:

It indicated that four rows have been deleted. You can execute the query that find duplicate emails again to verify the delete:

The query returns an empty set, which means that the duplicate rows have been deleted.

Let’s verify data from the contacts table:

The rows with id 2, 4, 7, and 9 have been deleted.

In case you want to delete duplicate rows and keep the lowest id, you can use the following statement:

Note that you can execute the script for creating contacts table again and test this query. The following output shows the data of the contacts table after removing duplicate rows.

How To Remove Duplicates In Oracle From Select

B) Delete duplicate rows using an intermediate table

The following shows the steps for removing duplicate rows using an intermediate table:

1. Create a new table with the structure the same as the original table that you want to delete duplicate rows.
2. Insert distinct rows from the original table to the immediate table.
3. Drop the original table and rename the immediate table to the original table.

The following queries illustrate the steps:

Step 1. Create a new table whose structure is the same as the original table:

Step 2. Insert distinct rows from the original table to the new table:

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

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

C) Delete duplicate rows using the ROW_NUMBER() function

The following statement uses the ROW_NUMBER() function to assign a sequential integer number to each row. If the email is duplicate, the row number will be greater than one.

The following statement returns id list of the duplicate rows:

And you just delete the duplicate rows from the contacts table using the DELETE statement with a subqueryin the WHERE clause:

MySQL issued the following message:

In this tutorial, you have learned how to delete duplicate rows in MySQL by using the the DELETE JOIN statement or an intermediate table.

Delete Duplicate Record in Oracle

How To Remove Duplicates In Oracle

Create table called Sports

Insert records into Sports table

Display Records

Re-insert same values into Sports table

Display Records again

Deleting Duplicate Records from Sports Table.

Display Records again, it has all unique rows,

Remove Duplicate Files Windows 10

Oracle Sql Delete From Query