Delete From - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Delete From

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-03 : 11:14:34

Guys,

I'm always cautious when it comes to deletes based on information from other tables.

CAn someone please advise best method from one of these, I want to remove records where the item ParentID from the Item table = the IncidentNumber from the incident table

Thanks

Option for best practice?

--option1
Delete From t
From dbo.MyItem t
Join dbo.Incident i
On t.ParentID = i.IncidentNumber
Where t.ParentID= i.IncidentNumber

--option2
DELETE FROM dbo.MyItem
WHERE EXISTS
( SELECT i.IncidentNumber
FROM dbo.Incident i
WHERE ParentID = i.IncidentNumber
)

--option3
DELETE dbo.MyItem FROM dbo.MyItem it
JOIN dbo.Incident i
On it.ParentID = i.IncidentNumber
Where it.ParentID = i.IncidentNumbe

We are the creators of our own reality!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-03 : 11:37:43

I think the Where clauses are redundant. You're joining on ParentId = IncidentNumber, so the only results after the (implied INNER) join are those where ParentId = IncidentNumber. You should be able to remove that clause.

As to which is better: Compare the execution plans and note the differences. If one uses seeks and another uses scans, take the one with seeks. After that, take the one that is the most succinct. For me, that would be option 1 with the WHERE Clause removed.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-03 : 12:18:45

Yeh I get your point although I normally leave the where clauses as extra checking constraints.
Thanks

We are the creators of our own reality!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-03 : 13:23:32

option1 and option3 are identical, doesn't matter if you use aliases or not. Deciding between option1 and option2 is more of a style. I'd say no such best practice exists. As gbritton mentioned, compare the execution plans. I would bet they are identical. I prefer joins unless it's an OUTER JOIN. When OUTER JOIN, I prefer the WHERE NOT EXISTS syntax as I think it's cleared. Using IS NULL for the OUTER JOIN isn't immediately clear to those reading the code later down the road.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-04 : 05:16:03

Thanks for the advise guys, maybe am being anal but its always good to get opinions, Im going with this one as I've used it many times before after looking thru my scripts.

also, this is the one Micsrodoft recommends on their site. Note I've changed the = to <> in this case.

Thanks

DELETE FROM dbo.MyItem
From dbo.FRS_MyItem it
JOIN dbo.Incident i
On it.ParentID = i.ItemNumber
Where it.ParentID <> i.ItemNumber
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));

We are the creators of our own reality!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-04 : 08:14:52

huh?! How can that possibly work? the JOIN ensures that the only records matched are those where it.ParentID = i.ItemNumber. The WHERE clause takes that and filters out those where it.ParentID <> i.ItemNumber. How can that ever be anything but an empty set?

Please remove the WHERE clause. It is redundant at best and gives incorrect results at worst -- as in this case!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources