SQL Server – Delete Syntax Explained

deletesql servert-sql

I recently faced a delete syntax I was unaware of.

delete #fooTbl from #fooTbl where attr ='some'

The Official Microsoft documentation states:

DELETE FROM [database_name . [ schema ] . | schema. ] table_name    
[ WHERE <search_condition> ]   
[ OPTION ( <query_options> [ ,...n ]  ) ]
[; ]

i have some perplexity about the command I wrote:

  • What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
  • What is the best practice?

Best Answer

What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?

Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.

A simple example of deleting from #footbl with an inner join to a different table

DELETE FT
FROM 
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

Whereas this generates an error due to not specifying what table to delete from

DELETE  
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword 'AS'.

And this also works

DELETE  #fooTbl
FROM #DifferentTable AS  DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';

In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.

What is the best practice?

In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.

Some more examples in the Microsoft documentation

D. Using joins and subqueries to data in one table to delete rows in another table

Using the table name twice

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;

Using an alias

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

Both serving the exact same purpose.

Related Question