simplified delete statement

Question

Hi Expert,

can someone explain me the query below

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

i want to simply this query without using from for 2 times ...but below query getting error

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

Answer 1

You can skip the first FROM, but not the second if you are going to use a JOIN later.

In ANSI syntax, FROM is mandatory and you need to say:

DELETE FROM tbl WHERE ...

But this FROM is optional in SQL Server.

On the other hand, the ANSI syntax does not permit for the FROM clause in DELETE but you would have to write something like:

DELETE FROM Sales.SalesPersonQuotaHistory
WHERE EXISTS (SELECT * 
             FROM  Sales.SalesPerson AS sp 
             WHERE Sales.SalesPersonQuotaHistory AS spqh
               AND Sales.SalesPersonQuotaHistory.BusinessEntityID = sp.BusinessEntityID
               AND Sales.SalesPersonQuotaHistory.date= sp.date 
               AND Sales.SalesPersonQuotaHistory.sourcesystem= sp.sourcesystem
               AND p.SalesYTD > 2500000.00);

And, yeah, since you cannot have a FROM clause, there is no provision to define an alias for the target table of the DELETE.

But this is ANSI-compliant SQL. And most of us, don't care if we are ANSI-compliant or not.