CODEWITHSUNDEEP
sql-servert-sql
undrline - Reinstate Monica
undrline - Reinstate Monica

Reputation: 576

SQL Server : DELETE FROM table FROM table

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

Upvotes: 5

Views: 17726

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 8

TomC
TomC

Reputation: 2804

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

Upvotes: 9

Related Questions