Check for duplicates in a database and delete them

I have a table structured as follows:

table(A, B)

They are both primary keys and they are needed to connect two entries in another table (i.e. they simbolize a friendship between users).

I need to check the table and, if (A,B) exists, delete an eventual (B,A) (or vice-versa).

Since the database is huge, I can't do this manually for every single entry each time.

Of course, I programmed the script t开发者_开发技巧hat populated the database to check for this situation and avoid it, but we've been using that script on 8 different PCs and so the different dumps may have "reverse duplicates".

---

The problem has arisen because the relationship you are trying to describe is symmetrical - but the schema models an asymmetric association. The right to model the problem would be to maintain a table of relationships - then have a table linking users to relationships, e.g.

relationship:
   id auto_increment

related:
   r_id foreign key references relationship.id
   u_id foreign key references user.id
   primary key (r_id, u_id)

But to clean up the existing data...an obvious approach would be...

DELETE FROM yourtable d
WHERE A>B AND EXISTS (
    SELECT 1 
    FROM yourtable r
    WHERE r.A=d.B
    AND r.B =d.A
)

However, if I recall correctly MySQL doesn't like using a subselect in a delete which references the same table as the delete. So....

SELECT d.A,d.B 
INTO dups
FROM yourtable d, yourtable r
WHERE d.A>d.B
AND r.A=d.B
AND r.B =d.A;

then....

DELETE FROM yourtable
WHERE EXISTS (
 SELECT 1 FROM dups
 WHERE dups.A=yourtable.A
 AND dups.B=yourtable.B
)

Not sure if the pushed predicate will still cause a problem, so if that doesn't work....

DELETE FROM yourtable
WHERE CONCAT(A, '/', B) IN (
 SELECT CONCAT(A, '/' B) FROM dups
)