Delete multiple rows in MySql without CTE

Question

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!

Some good examples here: https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — Michael Nelles, Jan 23 '20 at 22:26

score 1 · Accepted Answer · answered Jan 23 '20 at 22:16

1

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.

answered Jan 23 '20 at 22:16

GMB

Almost, but it deletet also more than it should, because now, there is nor Chevrolet Corvett anymore....https://justpaste.it/4lvpj – Tamiond Jan 23 '20 at 22:23
@Tamiond: I would doubt that. What if you run `select * from mytable where brand = 'Chevrolet' and model = 'Corvette'`? – GMB Jan 23 '20 at 22:27
1

I also tested this and it works. I suspect Tamiond did not run the same query. – Bill Karwin Jan 23 '20 at 22:27
1

Thanks, i just didn't spot it :/ – Tamiond Jan 23 '20 at 22:29
Welcome @Tamiond, no pb! – GMB Jan 23 '20 at 22:32

1 Answers1