DBA Myths: You can’t use an alias in a DELETE statement3
September 24, 2013 by Kenneth Fisher
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, Microsoft SQL Server, sql statements, T-SQL
3 thoughts on “DBA Myths: You can’t use an alias in a DELETE statement”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] alias’ which are somewhat common in more complex queries. Still not a problem as I discussed here and here. Believe it or not I’ve never run across a query I couldn’t easily convert […]
This should work too without the extra FROM command:
Yep. That works as well 🙂