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