Search code examples
mysqlduplicatesuniquephpbbbidirectional

Removing bidirectional duplicates in MySQL

I'm modifying phpBB's table to have bidirectional relationships for friends. Unfortuntately, people that have already added friends have created duplicate rows:

user1   user2   friend
2       3       true
3       2       true
2       4       true

So I'd like to remove rows 1 and 2 from the example above. Currently, this is my query built (doesn't work atm):

DELETE FROM friends WHERE user1 IN (SELECT user1 FROM (SELECT f1.user1 FROM friends f1, friends f2 WHERE f1.user1=f2.user2 AND f1.user2=f2.user1 GROUP BY f1.user1) AS vtable);

inspired by Mysql Duplicate Rows ( Duplicate detected using 2 columns ), but the difference is that I don't have the unique ID column and I'd like stay away from having an extra column.

Solution

  • Apologies if this isn't 100% legal MySQL, I'm a MSSQL user...

    DELETE F1
FROM friends F1
INNER JOIN friends F2
ON F2.user1 = F1.user2
AND F2.user2 = F1.user1
WHERE F1.user1 < F1.user2