0

I have a table follow. I need to delete duplicate records based on two columns user_id and follow_id please tell me a query for delete example:-

id   |   user_id  | follower_id  |  
148  |         3  |          31  |
163  |         3  |          31  |
Alfiza malek
  • 994
  • 1
  • 14
  • 36
  • 1
    Which `ID` you have to keep 148 or 163 – Praveen Aug 22 '15 at 07:50
  • 3
    possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) – Strawberry Aug 22 '15 at 07:53

2 Answers2

0

This query should identify all duplicates and remove all of them, remaining a single instance from each duplicate set, having the smallest id.

delete
from yourtable t1, yourtable t2
where (t1.user_id = t2.user_id) and (t1.follow_id = t2.follow_id) and (t1.id > t2.id)
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
0

This query worked.

select s.id, t.* 
from follow s
join (
select user_id, follower_id, count(*) as qty
from follow
group by user_id, follower_id
having count(*) > 1
) t on s.user_id = t.user_id and s.follower_id = t.follower_id
Alfiza malek
  • 994
  • 1
  • 14
  • 36