Delete Duplicate Rows with Multiple columns Mysql-CodePudding

I have a list of cars, and it will do a lot, the base is not small, about 70k. I want to delete repeated tables, can you tell me how this can be done? For example, if the model and make are repeated, it will delete and leave one.

Use Mysql 5.7.34 version

EXAMPLE:

id: 1, model: x5, make: bmw
id: 2, model: x5, make: bmw
id: 3, model: a5, make: audi
id: 3, model: a5, make: audi

RESULT:

id: 1, model: x5, make:bmw
id: 3, model a5, make: audi

I try use this query, but deletes all rows

DELETE FROM `car_db` WHERE `id` NOT IN (SELECT MAX(`id`) FROM `carsa` GROUP BY `make`, `model`)

Tell me or help me how to make the right request for my task, thanks

CodePudding user response：

We can delete using a join approach:

DELETE t1
FROM yourTable t1
LEFT JOIN
(
    SELECT model, make, MIN(id) AS min_id
    FROM yourTable
    GROUP BY model, make
) t2
    ON t2.model = t1.model AND
       t2.make  = t1.make  AND
       t2.min_id = t1.id
WHERE
    t2.min_id IS NULL;

The above logic will delete, for every group of records belonging to the same model and make, every record which is not the minimum id record.