Recently I wrote about the myth that you can’t use an alias in an UPDATE statement. You can of course, and the trick is to make sure that the alias is used in the FROM clause of the statement. That brought up the question “So how about the DELETE statement?”

Similar to the code I used to demonstrate the UPDATE statement here is a somewhat simple DELETE statement using AdventureWorks2008.

DELETE FROM HumanResources.EmployeePayHistory
WHERE EXISTS (SELECT 1 FROM HumanResources.Employee
WHERE HumanResources.Employee.BusinessEntityID = 
HumanResources.EmployeePayHistory.BusinessEntityID
AND HumanResources.Employee.HireDate < '1/1/1998');

So based on what we did with the UPDATE statement we should be able to put an alias in the FROM clause right?

DELETE FROM HumanResources.EmployeePayHistory AS EmpPay
WHERE EXISTS (SELECT 1 FROM HumanResources.Employee AS Emp
WHERE Emp.BusinessEntityID = 
EmpPay.BusinessEntityID
AND Emp.HireDate < '1/1/1998');

And as happened in the UPDATE statement we get an error.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword ‘AS’.

And the obvious assumption is that alias’ don’t work with DELETE statements. However it turns out that DELETE is actually the abbreviated form of DELETE FROM. In other words the word “FROM” here is not actually the FROM clause of the statement. The FROM for the FROM clause is the one that comes after the table name. So if you think about it you will in fact realize that the word FROM shows up twice in the DELETE statement. Confused anyone? Here is an example.

DELETE FROM EmpPay
FROM HumanResources.EmployeePayHistory AS EmpPay
WHERE EXISTS (SELECT 1 FROM HumanResources.Employee AS Emp
WHERE Emp.BusinessEntityID = 
EmpPay.BusinessEntityID
AND Emp.HireDate < '1/1/1998');

The nice thing here is that this let’s us re-write the statement a bit to use a JOIN as well.

DELETE FROM EmpPay
FROM HumanResources.EmployeePayHistory AS EmpPay
JOIN HumanResources.Employee AS Emp
ON Emp.BusinessEntityID = EmpPay.BusinessEntityID
AND Emp.HireDate < '1/1/1998');

Which is rather easier to read.

The moral to this story (and the previous one) is that before assuming that something won’t work you (and I) should make a point to read BOL and make sure.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL