Skip to content
Advertisement

Delete multiple rows in MySql without CTE

I have got a database with about 7000 cars, but unfortunately, only about 1000 are unique. Can I delete all the duplicated rows?

The schema looks like this:

this:

Thank you!

Advertisement

Answer

Here is one way to do it:

delete t1
from mytable t1
inner join mytable t2
    on  t2.brand = t1.brand
    and t2.model = t1.model
    and t2.id < t1.id

This will delete duplicates on (brand, model) while retaining the one with the smallest id.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement