image blog ol pro con key open source database 0

How to Select And Delete Duplicate Records in SQL?

In this tutorial we have discussed and shown displayed step by step method to select ad delete duplicate records using SQL. Anyone can easily follow through and master this method for using it for databases dealing with a huge array of data.

In order to find duplicate records and count them we can use “group_by” and “COUNT” clause.

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) AS Count
FROM Customers
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;

Now if few customers have identical first names and last names then we will exactly know how many are they. To delete them we need to use this query.

DELETE d1 FROM Dogs d1
INNER JOIN Dogs d2
WHERE
    d1.DogId < d2.DogId AND
    d1.FirstName = d2.FirstName AND
    d1.LastName = d2.LastName;

If you have any questions then write down in comments section please.