How to Find and Delete Duplicates in SQL
Database design best practices recommend using the UNIQUE constraint to prevent duplicates in a database. However, when working with a poorly designed database or unclean data, you may need to find duplicates yourself and delete them manually.
Read on to learn how to find duplicates in an SQL database and how to delete them.
Create a Sample Database
For demonstration purposes, create a table named Users with a name and score column by running this SQL query.
Insert some sample values by running this query:
Note that some of these rows contain duplicate values for the name column.
Feel free to check out theseSQL commands and queriesif you need a more in-depth explanation of how to manipulate databases using SQL.
Using GROUP BY to Find Duplicate Values
You canuse the GROUP BY statement to arrange valuesthat meet certain conditions in the same group.
Let’s say the names in the sample table have to be unique. You can use GROUP BY to group the rows sharing the same name.
COUNT lets you select the rows that have more than one user with the same name.
When you run this query, the database will return rows containing John and Jane as duplicates.
Deleting Duplicates From a Database
After finding the duplicates, you may want to delete them using the DELETE statement.
For this example, run the following query:
This query uses a CTE expression to find the duplicates and then deletes all of them except one.
Why You Should Delete Duplicate Data
Deleting duplicate data is not a must. However, it lets you free up the space that duplicate rows use.
Fewer rows also mean queries can execute much faster leading to higher performance. Use the queries in this tutorial to help you find and remove duplicates from an SQL database.
SQL is a sought-after skill. Here are a few ways to start a career in SQL and take advantage of the growth opportunities.
Don’t let aging hardware force you into buying expensive upgrades.
Lose your laptop without this feature, and you’ll wish you had turned it on.
My foolproof plan is to use Windows 10 until 2030, with the latest security updates.
So much time invested, and for what?
Windows is great, but adding this makes it unstoppable.
