October 22, 2011 at 1:31 pm
I have a problem where i need to delete extraneous similar rows from a database.
For example assume a table of LastName , FirstName.
For every LastName that exists I want to delete all rows where FirstName NULL.
Smith, Bill
Smith, NULL <---would get deleted because there is another Smith.
Jones, NULL <--- would NOT get deleted because there are no other Jones.
What I have is something like this:
DELETE A
FROM NamesTable A
WHERE FirstName IS NULL
AND LastName IN ( SELECT LastName
FROM NamesTable B
WHERE B.FirstName IS NOT NULL
AND A.LastName = B.LastName)
Is there a more efficient way?
October 22, 2011 at 1:50 pm
Other than the unnecessary join in the subquery (the IN does a 'join', no need to do it again), it looks fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 4:00 pm
Thanks Gail.
By the way; I attended your session last week @ SQLPass. I enjoyed it very much.
Do you ever wear shoes? 🙂
Regarding the extra join; it makes sense. Thanks for the tip.
Bob McC
October 22, 2011 at 4:14 pm
BobMcC (10/22/2011)
By the way; I attended your session last week @ SQLPass. I enjoyed it very much.
Awesome. Glad you liked it.
Do you ever wear shoes? 🙂
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 9:47 pm
I'm just kidding around.
If I'm not mistaken you gave your presentation barefoot, right?
Before that you were wearing Birkenstocks.
Wow... I'm scared of me. I swear I'm not some kind of foot fetish guy; and I have no idea why I noticed or remembered that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply