Problem: DELETE table_or_view FROM table_sources removes all rows

Question

Problem: DELETE table_or_view FROM table_sources removes all rows

gserdijn

Hall of Fame

Points: 3283
More actions
December 22, 2015 at 12:55 am

#306551

We recently discovered an error in a simple DELETE statement. Due to a missing line the (oversimplified) query was something like:
DELETE a FROM b WHERE b.id = 1;
All rows in table a were missing. Oops. BOL states (emphasis added):
The parameter table_or_view names a table or view from which the rows are to be deleted. (...) The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view.
The word additional seems to imply that statement is identical to:
DELETE a FROM a CROSS JOIN b WHERE b.id = 1;
The execution plans were identical. Both statements showed a Nested Loops operator with a No Join predicate warning.
This raises a few questions:
Is this behaviour correct? And is there a way to prevent or detect this? I sincerely hope that I do not have to review all DELETE statements in our application...
Dutch Anti-RBAR League

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes, the behaviour's correct. There's no predicate comparing a to b, and the predicate on b cannot filter the rows in a, therefore all rows qualify for the delete and hence all will be deleted.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101118 More actions · Answer 2

To insure consistency in how the DELETE is done, you should always use an alias for the affected table when using DELETE [table_name] ... FROM ....

For example:

DELETE a

FROM tbl_a a

<join_type> JOIN b ON ...

As a great side benefit, that also prevents the issue you had above. Ok, unless you deliberately use "old-style" joins and sloppy code:

DELETE a

FROM tbl_a a, b

WHERE b.<col> = 1

but you'd have to go out of your way to do it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

gserdijn Hall of Fame Points: 3283 More actions · Answer 3

Thank you, Gail and Scott, for the answers.

Much appreciated.

Dutch Anti-RBAR League