How to delete duplicates based on condition on 2 columns using SQL

Question

I have following table. I want to delete duplicate rows and keep the one that has Name1 = Name2. So, in the example below row 2 and 4 should be removed.

ID | Name1 | Name2
 1 | n1    |  n1
 1 | n1    |  n2
 2 | n1    |  n1
 2 | n1    |  n2

How can I do this with SQL query ?

Gordon Linoff · Accepted Answer · 2020-01-31 16:59:20Z

1

I think this does what you want:

delete from t
    where t.name1 <> t.name2 and
          exists (select 1
                  from t t2 
                  where t2.id = t.id and
                        t2.name1 = t2.name2
                 );

If you just want a select, that can be done similarly:

    select t.*
    from t
    where t.name1 = t.name2 or
          not exists (select 1
                      from t t2 
                      where t2.id = t.id and
                            t2.name1 = t2.name2
                     );

answered Jan 31, 2020 at 16:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jio Over a year ago

Thanks ! This works but can I ask you a related question here?

Collectives™ on Stack Overflow

How to delete duplicates based on condition on 2 columns using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related