Reputation: 1
This is friends table
userid(int) | friendid(int) | confirmed(tiny int)
--------------------
1 | 2 | 1
2 | 1 | 1
1 | 3 | 1
3 | 1 | 1
1 | 5 | 0
2 | 4 | 1
4 | 2 | 1
4 | 1 | 1
1 | 4 | 1
I have about 200k rows like this I want to delete duplicates example if userid=1 and friendid=2 then I dont want next row userid=2 friendid=1
I want unique combination across 2 columns means if 1,2 is there then 2,1 should not be there and should be deleted.
Then I can make friend list query like this:
select * from friends where (userid=1 or friendid=1) and confirmed=1
My questions:
How to delete this duplicate rows?
Please also give me query to find mutual friends of 2 user( userid of them may reside in either userid column or friendid in friends table).
Upvotes: 0
Views: 186
Reputation: 782785
Join the table with itself to find the rows with the same pair of users in the opposite order.
DELETE f1
FROM friends as f1
JOIN friends AS f2 ON f1.userid = f2.friendid AND f1.friendid = f2.userid
WHERE f1.friendid > f1.userid
The WHERE
clause ensure that we only delete one of the two rows in the set.
Upvotes: 1