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
As far as I understand, you would use a structure like this where you are restricting which rows to delete from the first table based on the results of the from query. But to do that you need to have a correlation between the two.
In your example there is no correlation, which will effectively be a type of cross join which means "for every row in tbl2, delete every row in tbl1". In other words it will delete every row in the first table.
Here is an example:
declare @t1 table(A int, B int)
insert @t1 values (15, 9)
,(30, 10)
,(60, 11)
,(70, 12)
,(80, 13)
,(90, 15)
declare @t2 table(A int, B int)
insert @t2 values (15, 9)
,(30, 10)
,(60, 11)
delete from @t1 from @t2
The result is an empty @t1.
On the other hand this would delete just the matching rows:
delete from @t1 from @t2 t2 join @t1 t1 on t1.A=t2.A
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.
I use it in the following context (removing duplicates):
delete from a
from
(select
f1
,f2
,ROW_NUMBER() over (partition by f1, f2
order by f1, f2
) RowNumber
from TABLE) a
where a.RowNumber > 1
DELETE FROM tbl, as there is no correlation defined between the two.