delete duplicate entries in table

Question

Accepted Answer

One way of doing this is by joining the table on a subquery using `LEFT JOIN`. The subquery gets the lowest `ID` for every `UID`. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

```
DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID
            FROM    TableName
            GROUP   BY uid
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID
WHERE   b.uid IS NULL
```

- SQLFiddle Demo

However, if the records of `UID` can have different name, then you need to include `name` on the `group by` clause or else only unique `uid` with the lowest `ID` will remain.

```
DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID, name
            FROM    TableName
            GROUP   BY uid, name
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID AND
                a.name = b.name
WHERE   b.uid IS NULL
```

- SQLFiddle Demo

- SQLFiddle Demo (the same uid with different name)

delete duplicate entries in table

Solution

Problem

Related problems