How can I delete rows based on just two column conditions.
Example
Table 1 id name phone 1 aa 123 1 aa 345 1 bb 123 2 aa 456 1 NULL 123 1 123 My Expected output
id name phone 1 bb 123 2 aa 456 My condition to delete: if id and name is same, delete the rows If one of the value in a condition is null or blank it should also delete the row as given in the input.
Delete from table1 t where exists ( Select * from (Select id, name from table1 group by id, name having count(*) > 1) t2 where t.id = t2.id and t.name = t2.name) This should do what you want. You can do the select first for testing purposes, then remove the Select and uncomment out the delete.
-- This joins on the table the set of data that has more then 1 row with duplicate IDs, and names. Then you can delete from here. --DELETE t1 SELECT * FROM Table1 T1 INNER JOIN ( -- this gets all the records that have more then 1 ID and Name that are the same. SELECT ID, name FROM Table1 GROUP BY ID, name HAVING COUNT(*) > 1 ) ToDelete ON T1.ID = ToDelete.ID AND T1.name = ToDelete.name 
create table #tablea ( id int, name varchar(3), phone int ) insert into #tablea (id, name, phone) values (1,'aa','123'), (1,'aa','345'), (1,'bb','123'), (2,'aa','456') select * from #tablea delete a from #tablea a inner join ( select id, name from #tablea group by id, name having COUNT(*) > 1 ) b on a.id = b.id and a.name = b.name select * from #tablea drop table #tablea 