how to delete all duplicate rows in mysql

Question

this is my table role_users for particular id

I want to delete all duplicate rows for role_id and user_id and if 2 or more records are there then only 1 latest record should be there and other should be deleted. How can i write this.?

Answer 1

You can use the delete ... from ... join... syntax:

delete r
from role_users r
inner join (
    select role_id, user_id, max(id) max_id
    from role_users
    group by role_id, user_id
) r1 
    on r.role_id = r1.role_id 
    and r.user_id = r1.user_id
    and r.id < r1.max_id