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
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)
as, mea culpa. See my edited answer.
DELETE a FROM MyTable a... (though I would also use MT as the alias).In SQL Server, DELETE with FROM syntax is a bit different. Try one of these:
delete @Results
from @Results r
where
r.AffectedEndUserName is null
and exists (
select
1
from @Results rr
where
rr.AffectedEndUserName is not null and rr.EmpId = r.EmpId
);
delete r
from @Results r
inner join @Results rr on rr.EmpId = r.EmpId and rr.AffectedEndUserName is not null
where
r.AffectedEndUserName is null;
I would prefer the latter; much cleaner.
