Problem: DELETE table_or_view FROM table_sources removes all rows

  • 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

  • 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
  • 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".

  • Thank you, Gail and Scott, for the answers.

    Much appreciated.


    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