Delete a row with duplicate field value but keep the later one in MySQL-CodePudding

I am pretty new to MySql and I want to run a query and delete the record of duplicate product_id and keep the latest one

id     rule_id       produc_id
6        3               2
7        3               3
8        4               2
9        4               5
10       4              9

This my table and I want to delete the record of id=6 because the product_id here, is repeated again in id=8 but I want to keep the record of id=8...

Also, the query should be flexible enough so that if I add another record in the table with repetitive product_id again then the query should delete the record with id=8 and keep the latest record... Thanks in advance...

CodePudding user response：

You may use exists logic here:

DELETE
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.produc_id = t1.produc_id AND
                    t2.id > t1.id);

In plain English, the above query says to delete any record for which we can find another record with the same produc_id value but whose id value is greater (later) than the record being considered for deletion.

Note that it would probably be better to have a dedicated timestamp column for the time of record creation, rather than relying on the id column.

CodePudding user response：

DELETE t1
FROM table t1
JOIN table t2 USING (produc_id)
WHERE t1.id < t2.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c2724a7b82bc78e7f6ecbaeeeabc2f6