Post reply

Problem: DELETE table_or_view FROM table_sources removes all rows

  • 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

  • December 22, 2015 at 1:03 am

    #1847347

    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

  • December 22, 2015 at 10:19 am

    #1847508

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • December 24, 2015 at 1:48 am

    #1847882

    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