DELETE FROM Where?

skass — Thu, 30 Apr 2009 21:48:00 GMT

For years, SQL Server has supported a (second) FROM clause in UPDATE and DELETE statements. Its behavior isn't always deterministic, a fact Microsoft points out in the documentation.

Today, someone was surprised by the basic semantics of DELETE .. FROM. This probably happens a lot, but fortunately today's surprisee posted this Connect item because the following statement didn't do what the documentation seemed to say it would:

DELETE FROM t2
FROM t1 AS t2

Quick now, what table does this truncate?

You're in good company if you said t2, which is the wrong answer. The statement truncates t1, however, not t2. In fact, this DELETE statement contains no reference to the table t2, assuming there even is one. Here, t2 is only an alias, and the statement is semantically identical to

DELETE FROM ThisIsAnAliasNotATable
FROM t1 AS ThisIsAnAliasNotATable

When there are two FROM clauses in a DELETE statement, the first FROM clause is interpreted in the context of the table source defined by the second one, which is analogous to how SELECT works. The following query selects rows from t1, not t2, and that's no surprise:

SELECT t2.a
FROM t1 AS t2

If you think about it, you should agree that different behavior for DELETE would be very bad. If SQL Server produced what Vitaliy expected, someone could create tables with names used somewhere as aliases, and cause UPDATE and DELETE statements to stop updating the tables they were supposed to update. What makes the DELETE behavior more confusing than the SELECT behavior is the fact that in the case of DELETE, the keyword FROM can be used twice. It you write DELETE .. FROM statements with two FROMs, remember that the bottom FROM clause is the "outer" one in the sense of scope. It should be considered first, as is clearer in this DELETE statement, which is equivalent to the one that suprised Vitaliy:

WITH t2 AS (
SELECT * FROM t1
)
DELETE FROM t2

For the record, this CTE-based DELETE is just as non-standard as the DELETE .. FROM, but it's less confusing. If you can't easily (or effeciently) avoid the use of DELETE .. FROM, consider rewriting DELETE .. FROM as a CTE to reduce the chance of confusion.

Steve Kass : T-SQL language extensions, T-SQL, nonstandard

DELETE FROM Where?