MYSQL - Deleting values that can appear in reverse order in 2 columns

I have 2 columns, name1 and name2, that contain numerous rows of possible names.

For example: John Citizen, Jane Taxpayer, Citizen John, Taxpayer Jane, etc...

One partial name is stored under name1 and another partial name is stored under name2. I was wondering how would I be able to delete all of the names that appear in a table so that there is only one entry for every possible name? For example John Citizen or Citizen John would exist, but not both. So in the end I would get:

John Citizen
Jane Taxpayer

or...

John Citizen
Taxpayer Jane

or...

Citizen John
Taxpayer Jane

or...

Citizen John
Jane Taxpayer

EDIT:开发者_运维技巧 Just to clarrify, name1 = John, name2 = Citizen for example. They've already been broken up into their respective columns so I just need to select unique combinations between the two columns.

ok, rewriting the whole thing. Given a table n:

create table n(f varchar(10), l varchar(10), primary key(f,l));

with the following 10 rows:

'alfred', 'hitchcock'
'hitchcock', 'alfred'
'john', 'doe'
'doe', 'john'
'edward', 'scissorhan'
'felix', 'leclerc'
'lane', 'penny'
'penny', 'lane'

This query will return only the names that don't have reverses:

SELECT u1.f, u1.l
FROM n u1
LEFT JOIN n u2
ON (u1.f=u2.l AND u1.l=u2.f)
WHERE u2.f IS NULL and u2.l IS NULL;

'edward', 'scissorhan'
'felix', 'leclerc'

And this query will return only the names that have reverses (and the reverses):

SELECT u1.f, u1.l
FROM n u1 JOIN n u2
ON (u1.f=u2.l AND u1.l=u2.f);

'alfred', 'hitchcock'
'doe', 'john'
'hitchcock', 'alfred'
'john', 'doe'
'lane', 'penny'
'penny', 'lane'

So the set we need to work with is created by the above query. If we decide that we only want the rows where u1.f < u2.f, then we have a filter that gets us a unique set:

SELECT u1.f,  u1.l
FROM n u1
JOIN n u2
ON (u1.f=u2.l AND u1.l=u2.f)
WHERE u1.f<=u2.f;

You can do a self join to find the matching pairs of rows, and then arbitrarily pick which one to delete by discriminating on one of the two name columns. If you have a primary key you can discriminate on that, but I don't see one in your example.

You should also add a unique index on (fname,lname) if you do not have one already to prevent exact duplicates from being inserted.

Assuming you have the unique index on (fname,lname), this query should work for you:

DELETE t2.*
FROM uniquenames t1
INNER JOIN uniquenames t2 
  ON t2.lname = t1.fname AND t2.fname = t1.lname AND t2.fname < t1.fname

Please not that if you don't have a unique index on (fname,lname), then you could have two rows that are exact duplicates. "Kyle Kyle" and "Kyle Kyle" for example. My delete statement would not de-dupe those two rows. If you have a primary key that's easy, but I don't see one in your example.