MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (
  name varchar(20),
  name2 varchar(20)
);

insert into mytable values
('Elizabeth Black', 'Alice Lewis'),
('Elizabeth Black', 'Jack Green'),
('Jack Green', 'Elizabeth Black'),
('sali Black', 'bob Black');

Records: 4  Duplicates: 0  Warnings: 0


select *, greatest(name, name2), least(name, name2)
from mytable


name
name2
greatest(name, name2)
least(name, name2)


Elizabeth Black
Alice Lewis
Elizabeth Black
Alice Lewis

Elizabeth Black
Jack Green
Jack Green
Elizabeth Black

Jack Green
Elizabeth Black
Jack Green
Elizabeth Black

sali Black
bob Black
sali Black
bob Black


select greatest(name, name2) as first_name, least(name, name2) as second_name
from mytable
group by greatest(name, name2), least(name, name2)
having count(1) > 1


first_name
second_name


Jack Green
Elizabeth Black


DELETE t.* FROM mytable t
INNER JOIN (
  select greatest(name, name2) as first_name, least(name, name2) as second_name
  from mytable
  group by greatest(name, name2), least(name, name2)
  having count(1) > 1
) as s ON (t.name = s.first_name and t.name2 = s.second_name)
          OR (t.name2 = s.first_name and t.name = s.second_name)
select *
from mytable


name
name2


Elizabeth Black
Alice Lewis

sali Black
bob Black

name	name2	greatest(name, name2)	least(name, name2)
Elizabeth Black	Alice Lewis	Elizabeth Black	Alice Lewis
Elizabeth Black	Jack Green	Jack Green	Elizabeth Black
Jack Green	Elizabeth Black	Jack Green	Elizabeth Black
sali Black	bob Black	sali Black	bob Black