How to remove duplicate rows in SQL.
In this video, you'll learn how to delete duplicate records in SQL.
If you've got duplicate records in your table, and want to delete the duplicates, you can
use one of the methods in this video to do that.
Let's get into the video.
Let's say you have a table of data that has some duplicates, in this video we'll use this
example of people.
This table here has a few columns, and we can see that there are some duplicate rows.
What can we do to remove these duplicate rows?
First, let's write a query to find our duplicate rows.
This query here will find the count of records that have the same values.
The having clause ensures that are only rows with a count greater than one are shown.
The columns in the select clause and the group by clause are those that indicate a row is
a duplicate.
In this example, if a row has the same first name and last name as another row, it's a
duplicate.
In your table it may be a different set of columns.
This shows the records that have duplicates.
You can see that there are some duplicate rows.
So how do we delete them?
We can write a statement like this.
We can delete from one table, join to the same table where the ID is less than the joined
ID.
We join on the fields that are used to identify if a row is a duplicate.
In this case, it's first name and last name.
And the ID column is your primary key or the unique identifier for the row.
There needs to be a way to delete all but one row.
And if you have an ID field or some kind of primary key, you can use that here.
If you run this statement, you'll see that some rows are deleted.
You can then select from a table again using the same query as before to see if any duplicates
are shown.
The results here should indicate that there are no duplicates.
So this method can work well, but it may not work in your database if you don't have an
ID, or if it doesn't allow two tables in a delete statement.
There is another way.
Another way to delete duplicate records is to add the unique records into a table and
use the new table to replace the old table.
So we start off by creating the new table.
We create a new table as a select statement and this select statement will select the
distinct records from the other table.
We want to only insert one row for each of the records.
So we want to use a group by to help with this.
Or we can use a distinct depending on the data you want to load into the new table.
You may also need to use a max function to get the ID if that's a field that you want
to keep.
Once you've created and populated the new table, you can delete the old table and rename
the new table to the old table.
The syntax would probably look like this, but different databases have different ways
of renaming tables.
So now your person table no longer has duplicates.
This can work if you're able to identify the duplicates and can use different ID values.
What if you can't do this?
There is a third method you can use.
The third method of deleting duplicate rows is to use the row number function available
in several database vendors.
You can use the row number function inside a subquery to find the row number and delete
all but the first row.
Your query would look similar to this.
It's a delete statement that deletes from the person table where the ID is found in
the subquery.
In the subquery, you can use the row number function.
In the partition by clause you specify the columns that make it a duplicate.
This could be the first name and the last name, like in our example, or whatever columns
are used in your table.
When you run this query, you delete all duplicate rows in your table.
You can then run the select query from earlier in this lesson to see that no duplicates remain.
So that's how you can delete duplicate records in SQL.
There are a few different methods, depending on if you have an ID field, or which database
vendor you use.
If you've learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com.
That's where I share my best database related content.
Which method from this tutorial was the most helpful for you?
Was it the first method with joining the table to itself, method two where we created a new
table, or method three, which uses the row number?
Thanks for watching.