DBA Myths: You can’t use an alias in a DELETE statement

1

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.

One thought on “DBA Myths: You can’t use an alias in a DELETE statement

  1. […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: