The solution consists in first place to find exactly where the "bad registers" are. If we see the records (BG, CH, 280) and (CH,BG,280), the problems consists that are "equal" if they were inserted always both fields in alfabetical order that is (BG, CH,280). So a record is not good in first opinion, if name2 is
previous to name1.
The query:
SELECT
CASE WHEN t1.name1<>(CASE WHEN t1.name1<t1.name2 THEN t1.name1 ELSE t1.name2 end) THEN 'bad register' else 'good registerk' end as clase
,t1.*
FROM table1 t1
says that (CH, BG, 280), (HY,CH, 350) are 'wrong register' (what is right), but also says that (CH,CBE, 500) is a 'wrong register' (what is worng).
This last record really isnt´t a 'wrong register' because there isn´t a 'mirror' register (CBE, CH, 250)
So if we use the query:
SELECT
CASE WHEN t1.name1<>(CASE WHEN t1.name1<t1.name2 THEN t1.name1 ELSE t1.name2 end) THEN 'wrong register' else 'ok register' end as clase
,t1.*
FROM table1 t1
JOIN table1 t2
ON t1.name1=t2.name2 AND t1.name2=t2.name1
we only have the real 'wrong register' (because if there is no 'mirrow' register it says that it´s a 'ok register')
Then, the query:
SELECT t1.*
FROM table1 t1
JOIN table1 t2
ON t1.name1=t2.name2 AND t1.name2=t2.name1
WHERE t1.name1<>(CASE WHEN t1.name1<t1.name2 THEN t1.name1 ELSE t1.name2 end)
just list the records that you need to delete.
or simplier:
SELECT t1.*
FROM table1 t1
JOIN table1 t2
ON t1.name1=t2.name2 AND t1.name2=t2.name1
WHERE t1.name1>t1.name2
Finaly the order:
delete TABLEdELETE
FROM table1 tableDelete
inner JOIN table1 t1
on tabledelete.name1=t1.name1 and tabledelete.name2=t1.name2
inner JOIN table1 t2
ON ( t1.name1>t1.name2 AND t1.name1=t2.name2 AND t1.name2=t2.name1 )
deletes the register that you want.
Note: If your data are:
BG CH 1
CH BG 2
CH HY 2
HY CH 3
and you don´t want that the record CH, BG, 2 will be delete then use:
delete TABLEdELETE
FROM table1 tableDelete
inner JOIN table1 t1
on tabledelete.name1=t1.name1 and tabledelete.name2=t1.name2
inner JOIN table1 t2
ON ( t1.name1>t1.name2 AND t1.name1=t2.name2 AND t1.name2=t2.name1 AND t1.distance=t2.distance )