How to delete null column data in SQL Server

Question

In my stored procedure I have a temp table where I have data like below:

I am getting 2 rows for some employees one having emp name and other are null.

Now I need to delete the row for employee if its having duplicate rows with null emp name.

We don't need to delete if its the single row with null. I just need to delete the highlighted ones.

Please help what is the where condition here

Answer 1

You can check for name with the is null operator, and have another exists condition to check for a corresponsing id with a non-null name:

DELETE a
FROM   mytable a
WHERE  emp_name IS NULL AND
       EXISTS (SELECT *
               FROM   mytable b
               WHERE  b.emp_name IS NOT NULL AND a.emp_id = b.emp_id)