Monday, December 1, 2008

Despite how odd it looks at first glance, it is legal SQL (at least, extended T-SQL). The oddness is because the DELETE statement can have two optional FROM clauses: in the code above, we haven't used the first one, but have used the second.

The first FROM clause "is an optional keyword that can be used between the DELETE keyword and the target table_name..." (BOL). The second FROM allows you to specify data from another, JOINed table, and delete corresponding rows from the table in the first FROM clause.

Personally I think writing this statement as:

DELETE FROM Customer
FROM Invoice
WHERE Customer.CustomerIndex = Invoice.CustomerIndex

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.