Mfcttrf

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?

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.