Removing duplicate SQL records to permit a unique key

Question

I have a table ('sales') in a MYSQL DB which should rightfully have had a unique constraint enforced to prevent duplicates. To first remove the dupes and set the constraint is proving a bit tricky. Table structure (simplified): - 'id (unique, autoinc)' - product_id The goal is to enforce uniqueness for product_id. The de-duping policy I want to apply is to remove all duplicate records except the most recently created, eg: the highest id. Or to put another way, I would like to delete only duplicate records, excluding the ids matched by the following query whilst also preserving the existing non-duped records: ``` select id from sales s inner join (select product_id, max(id) as maxId from sales group by product_id having count(product_id) > 1) groupedByProdId on s.product_id and s.id = groupedByProdId.maxId ``` I've struggled with this on two fronts - writing the query to select the correct records to delete and then also the constraint in MYSQL where a subselect FROM clause of a DELETE…

Accepted Answer

In reply to your comment, here's a query that works in MySQL:

```
delete YourTable
from YourTable
inner join YourTable yt2
on YourTable.product_id = yt2.product_id
and YourTable.id < yt2.id
```

This would only remove duplicate rows. The `inner join` will filter out the latest row for each product, even if no other rows for the same product exist.

P.S. If you try to alias the table after `FROM`, MySQL requires you to specify the name of the database, like:

```
delete <DatabaseName>.yt
from YourTable yt
inner join YourTable yt2
on yt.product_id = yt2.product_id
and yt.id < yt2.id;
```

Removing duplicate SQL records to permit a unique key

Solution

Problem