CODEWITHSUNDEEP
mysql
panchayat
panchayat

Reputation: 1

mysql unique combination of two columns

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

Answers (1)

Barmar
Barmar

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

Related Questions