SQL Remove Duplicate Rows: A How-To Guide

Sometimes you'll be working with a database table and you'll notice some duplicate records in the table.

If it's a small table, you can just use a DELETE statement to delete specific records.

However, if it's a large table, then this may not be possible. Well, it could be possible, but it would take a long time.

Fortunately, you can use the power of SQL to remove duplicate records.

There are several ways you can remove duplicate records in SQL. Some of them work in all database vendors (e.g. Oracle, SQL Server, MySQL, and PostgreSQL), and others only work in some vendors.

But you should be able to use at least one of the methods here in your database.

For each of these methods, you'll be able to run a SELECT query to verify the records to delete first, which helps to ensure your query is correct.

In this video, you'll see an example of a table with duplicate records, and see several methods and sample queries for deleting duplicate data.

Each of the methods automatically works out the duplicate rows, so there's no need for you to specify values in an IN clause or anything.

[johnPUAlifestyle]

Take a lot time finding the right video

[naanlopkromtit]

If you accidentally delete some of the duplicated rows from your table, what is a statement that I can run to add the columns back to my table?

[rohitsethi]

hello where id came this is not exist in table

[brianferris]

Great stuff. I have to figure how to do this after I append 3 queries using a CTE.

[LA-swgv]

Inner Join is definitely the least complicated version

[vicvic]

The first method doesn't work in my case...
I have got a different example, i.e. a different database, but this code:
delete from contacts c
inner join contacts d
on c.first_name = d.first_name and c.last_name = d.last_name and c.email = d.email
where c.contact_id < d.contact_id;
gives me an error: 'Incorrect syntax near 'c'.'
Actually, it is a correct syntax, imo. If I changed the 'delete from' word into 'select', everything works, but I want to delete and this doesn't work and I have no clue why. Could you help me, please?

[DraikM]

kept getting a syntax error for the INNER JOIN part, not sure what was wrong but this didn't work

[berthagarcia]

Does this work if we need to delete duplicates from multiple tables?

[abhay]

WHAT IF FIRST_NAME, LAST_NAME AND ID ARE SAME FOR EMPLOYEES? WHAT THE QUERY TO BE USED?

[ereshadul]

How about using window function to delete the duplicate data?

[Anilkumar-reddy]

Hi, I am having scenerio, I am having table having duplicate records, Based on date, i want to make one record as inactive record. How to do that?. Please help

[sindhuvi]

can someone explain why we using here p1.id < p2.id? I find it confusing