Delete duplicate rows with MySQL or MSSQL Delete duplicate rows with MySQL or MSSQL

The following article will provide a single SQL query that will delete rows that are duplicated in your table. Before you begin, be sure you've tracked down the bad code that is causing this; otherwise, you will need to run it multiple times!

To start, I will create a basic table structure that will help understand the SQL query:



Users
id (Primary Key)
username
email

After running with this table for a period of time, users began registering with the same email multiple times. A business logic decision has now been made that determines the email must be unique. Logical solution is to alter the database and add a unique index on the email field.

Uh oh! Now we've just received a nice database error indicating that the column contains duplicate records and you must clean them up before you can apply the unique index to that column.

Leveraging the MIN and MAX MySQL Functions

Bring on the SQL! The following query is going to group the duplicate records together and then using the MIN or MAX function, the duplicate rows will be deleted, leaving the id with the minimum or maximum id; depending on the group function you choose.


DELETE Users FROM Users
LEFT OUTER JOIN (
SELECT MIN(id) as UniqueId, email
FROM Users
GROUP BY email
) as RowsToKeep ON Users.id = RowsToKeep.UniqueId
WHERE RowsToKeep.UniqueId IS NULL

In the above SQL, only the minimum id will be selected from the users table which means that all of the other rows that contain the same email will be NULL allowing the delete statement to only delete those rows and keeping, in this example, the record with the minimum id. Swapping out the MIN function with the MAX function, would then keep the record with the maximum id instead; typically meaning the last record created versus the first record created.

Published on Feb 27, 2019

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | delete

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.