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


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.

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.

4 thoughts 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 […]

  2. […] which are somewhat common in more complex queries. Still not a problem as I discussed here and here. Believe it or not I’ve never run across a query I couldn’t easily convert between a […]

  3. freezer27 says:

    You can do it without use “WITH” using this:

    SET T.FIELD1 = ‘Something’
    FROM MyTable T
    WHERE T.FIELD2 > 0

Leave a Reply to Test your UPDATEs and DELETEs before you run them | SQL Studies Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,142 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: