Eliminating duplicates with specific conditions in SQL ?

Yogesh Kumar
04-May-2022
0

This will do the trick (if the table is duplicates). If you want to delete the rows:

 delete from duplicates d 
where first_name is null and last_name is null 
and 1 < (select count(*) from duplicates where phone_number = d.phone_number);

If you just want to get the new relation:

 select * from duplicates d 
where not (first_name is null and last_name is null 
and 1 < (select count(*) from duplicates where phone_number = d.phone_number));