The following examples use SQL to delete duplicate rows that have a primary key or unique identifier column.

Specifically, the examples delete all duplicates except one. So for example, in the case of three identical rows, it deletes two of them and keeps one.

Sample Data

Suppose we have a table with the following data:

SELECT * FROM Dogs;

Result:

+---------+-------------+------------+
| DogId   | FirstName   | LastName   |
|---------+-------------+------------|
| 1       | Bark        | Smith      |
| 2       | Bark        | Smith      |
| 3       | Woof        | Jones      |
| 4       | Ruff        | Robinson   |
| 5       | Wag         | Johnson    |
| 6       | Wag         | Johnson    |
| 7       | Wag         | Johnson    |
+---------+-------------+------------+

The first two rows are duplicates and the last three rows are duplicates (not counting the first column – the primary key).

MariaDB, PostgreSQL, SQLite, & SQL Server

Here’s an example of deleting duplicates from the above table.

This example works in SQL Server, MariaDB, PostgreSQL, and SQLite.

DELETE FROM Dogs 
WHERE DogId IN (
    SELECT DogId FROM Dogs 
    EXCEPT SELECT MIN(DogId) FROM Dogs 
    GROUP BY FirstName, LastName
    );

SELECT * FROM Dogs;

Result:

+---------+-------------+------------+
| DogId   | FirstName   | LastName   |
|---------+-------------+------------|
| 1       | Bark        | Smith      |
| 3       | Woof        | Jones      |
| 4       | Ruff        | Robinson   |
| 5       | Wag         | Johnson    |
+---------+-------------+------------+

Oracle

The previous example almost works in Oracle. But in Oracle, we need to replace EXCEPT with MINUS:

DELETE FROM Dogs 
WHERE DogId IN (
    SELECT DogId FROM Dogs 
    MINUS SELECT MIN(DogId) FROM Dogs 
    GROUP BY FirstName, LastName
    );

SELECT * FROM Dogs;

Result:

+---------+-------------+------------+
| DogId   | FirstName   | LastName   |
|---------+-------------+------------|
| 1       | Bark        | Smith      |
| 3       | Woof        | Jones      |
| 4       | Ruff        | Robinson   |
| 5       | Wag         | Johnson    |
+---------+-------------+------------+

MySQL

MySQL doesn’t support the EXCEPT operator or the MINUS operator. Therefore we need to construct a different query altogether.

The following query gets the job done:

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

SELECT * FROM Dogs;

Result:

+---------+-------------+------------+
| DogId   | FirstName   | LastName   |
|---------+-------------+------------|
| 1       | Bark        | Smith      |
| 3       | Woof        | Jones      |
| 4       | Ruff        | Robinson   |
| 5       | Wag         | Johnson    |
+---------+-------------+------------+