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 an UPDATE statement.

I’ve found a very common belief among users of T-SQL (both DBAs and Developers) is that you can’t use an alias with the UPDATE statement. As best I can tell the reason for this is because of a simple misunderstanding of the UPDATE command syntax.

Here is an example of a relatively simple update statement using AdventureWorks2008.

UPDATE HumanResources.EmployeePayHistory
SET Rate = HumanResources.EmployeePayHistory.Rate * 1.1, RateChangeDate = GETDATE()
FROM HumanResources.Employee
WHERE HumanResources.Employee.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID
  AND HumanResources.Employee.HireDate < '1/1/1998';

This is certainly a candidate for using an alias. So here a fairly common approach that I see to apply alias’ to this command.

UPDATE HumanResources.EmployeePayHistory AS PayHist
SET Rate = Rate * 1.1, RateChangeDate = GETDATE()
FROM HumanResources.Employee AS Emp
WHERE Emp.BusinessEntityID = PayHist.BusinessEntityID
  AND Emp.HireDate < '1/1/1998';

And this returns the error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘AS’.

So obviously alias’ don’t work on an UPDATE right? Well actually that’s because in order to use an alias you have to set up the UPDATE statement a little bit differently. Alias’ are only available in the FROM clause of the UPDATE statement, so if you list the table being updated in the FROM clause then the alias will work.

UPDATE PayHist
SET Rate = PayHist.Rate / 1.1, RateChangeDate = GETDATE()
FROM HumanResources.EmployeePayHistory PayHist
JOIN HumanResources.Employee Emp
	ON Emp.BusinessEntityID = PayHist.BusinessEntityID
WHERE Emp.HireDate < '1/1/1998';

One interesting note, unlike anywhere else in the statement the table name right after the UPDATE statement may be the full table name OR the alias.


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...