Why does this work? Delete with multiple froms no subquery

user_9458072 0
Viewed 72

I am not sure if this is a bug in SQL Server 2012.

I have a simple query:

DELETE 
 FROM TABLE1
 FROM TABLE2
WHERE TABLE1.COL1 = 1

In SSMS, this code parses without errors and deletes the data from Table1 without errors or warnings. My expectation was that SQL Server would throw an error because of multiple FROM statements without usage of subquery syntax.

tsql sql-server
1 Answers

In the syntax for DELETE, the first FROM clause specifies which table to delete. The actual keyword FROM is optional.

The second FROM can be specified if a joined deletion is necessary, so it functions the same as FROM in a SELECT. In such a query, the first FROM then specifies which of the joined tables to delete rows from, as the joined rows are fed through.

However, it is also valid, if somewhat ambiguous, to specify a table in the first FROM which is not present in the second FROM. This effectively causes a cross-join between the two clauses. Without any further WHERE clause, it will delete all rows from the table.

In your query, you have

DELETE 
 FROM TABLE1   -- table to delete rows from
 FROM TABLE2   -- cross-join all these rows
WHERE TABLE1.COL1 = 1   -- filtering

So TABLE2 is cross-joined, and then completely ignored.

Normally a joined DELETE should look like this:

DELETE FROM t1   -- should use alias here
FROM TABLE2 t2
JOIN TABLE1 t1 ON some_join_condition  -- normal join
WHERE t1.COL1 = 1   -- other filtering
user_148689970
Related