Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...