SQL Server : DELETE FROM table FROM table

Question

I keep coming across this DELETE FROM FROM syntax in SQL Server, and having to remind myself what it does.

DELETE FROM tbl
FROM #tbl
  INNER JOIN tbl ON fk = pk AND DATEDIFF(day, #tbl.date, tbl.Date) = 0

EDIT: To make most of the comments and suggested answers make sense, the original question had this query:

DELETE FROM tbl
FROM tbl2

Answer 1

I haven't seen this anywhere before. The documentation of DELETE tells us:

FROM table_source Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from and deleting the corresponding rows from the table in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

Later in the same document we find

D. 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.

With these examples to demonstrate the difference

-- SQL-2003 Standard subquery

DELETE FROM Sales.SalesPersonQuotaHistory   
WHERE BusinessEntityID IN   
    (SELECT BusinessEntityID   
     FROM Sales.SalesPerson   
     WHERE SalesYTD > 2500000.00);

-- 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;  

The second FROM mentions the same table in this case. This is a weird way to get something similar to an updatable cte or a derived table

In the third sample in section D the documentation states clearly

-- No need to mention target table more than once.

DELETE spqh  
  FROM  
        Sales.SalesPersonQuotaHistory AS spqh  
    INNER JOIN Sales.SalesPerson AS sp  
        ON spqh.BusinessEntityID = sp.BusinessEntityID  
  WHERE  sp.SalesYTD > 2500000.00;  

So I get the impression, the sole reason for this was to use the real table's name as the DELETE's target instead of an alias.