September 16, 2013 by Kenneth Fisher
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.