By s.demuro


2019-06-08 16:09:09 8 Comments

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?

1 comments

@Randi Vertongen 2019-06-08 16:38:13

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 Questions

Sponsored Content

1 Answered Questions

[SOLVED] SQL Server 2012 Import Wizard appending new data only

1 Answered Questions

0 Answered Questions

1 Answered Questions

[SOLVED] User-shared queries: Dynamic SQL vs. SQLCMD

3 Answered Questions

[SOLVED] Restoring SQL Server databases - tips and tricks?

0 Answered Questions

How to group SQL Server files for restoring database

1 Answered Questions

3 Answered Questions

[SOLVED] MySQL Large DELETE with JOINs

1 Answered Questions

[SOLVED] SQL Server — optimization techniques for rebuilding very large table

Sponsored Content