今天朋友问我一个DELETE语句:
DELETE FROM TABLEA FROM TABLEB WHERE TABLEA.X=TABLEB.Y
我一看,就说手误吧,DELETE后面能有两个FROM吗?
他说他也是第一次看到,但是这个语句是SQLSERVER生产库里的。
请教各位达人,这语句是啥意思?
个人认为如果这语句没问题,就是级联删除TABLEA,TABLEB里满足TABLEA.X=TABLEB.Y的记录。但是两个FROM真让人百思不得其解
这是它的语言扩展
https://msdn.microsoft.com/en-us/library/ms189835.aspx
C. Using joins and subqueries to data in one table to delete rows in another table
The following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database are deleted based on the year-to-date sales stored in the SalesPerson table. The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQL FROM extension to join the two tables.
-- SQL-2003 Standard subquery
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
GO