DBA Myths: You can’t use an alias in an UPDATE statement.

1

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.

About these ads

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

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

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

Follow

Get every new post delivered to your Inbox.

Join 527 other followers

%d bloggers like this: