Removing bidirectional duplicates in MySQL

Question

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 http://stackoverflow.com/questions/1799518/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.

Answer 1

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

Answer 2

DELETE r
FROM friends l, friends r
WHERE l.user1 = r.user2
AND l.user2 = r.user1

This deletes both entries. If you like to keep on of them you have to add a where statement like Will A alread proposed, but i suggest you to use > instead of < to keep the smaller user1 id. Just looks better :)