Aliased tables and updates... shouldn't this code fail?

Question

Once a table is aliased, it needs to be referred to by the alias, right?

This will fail...

Use AdventureWorks;

SELECT TOP 10 Store.Name FROM Store as st

...But this update would not.

Use AdventureWorks;

UPDATE Store
SET Name = Name + '$Test'

FROM Store as st

Does SQL Server parse any action verbs before it begins it's order of precedence of select operations?

Why do you have a FROM clause? You've already referenced the table you want to update - it's in the UPDATE clause. If you're trying to update based on a join, see [this question](http://stackoverflow.com/q/1604091/61305) - that's the only scenario where I think you'd need to reference the table with an optional FROM. — Aaron Bertrand, Oct 13 '14 at 22:17

score 4 · Answer 1 · answered Oct 13 '14 at 19:19

4

In

UPDATE Store
SET Name = Name + '$Test'
FROM Store as st

you are referencing two tables. You are referencing Store two times.

UPDATE Store
SET Name = Name + '$Test'
FROM sys.objects as st

would work just as well. This is a cross-product of two tables that is being updated.

In general, the alias rule applies to DML as well.

answered Oct 13 '14 at 19:19

usr

huh I am confused. Why am I referencing two tables? I thought I was updating Store in adventure works. you lost me with system objects. The code above isn't referencing any system tables. – discosammy Oct 13 '14 at 19:21
Look at it this way, you can write `UPDATE Store SET Name = Name + "Test"`, so when you add in the extra alias in the FROM clause, it's adding the table to your query again, hence the cross-product. – DavidG Oct 13 '14 at 19:28
Try it out. Put sys.objects into your query. It still runs. You'll find a small difference in the query plan. Just the fact that you used the same table name two times does not mean that you reference one table. It just looks like it. – usr Oct 13 '14 at 19:33
If that's the case, why am I able to use the alias in the update statement instead of the table name and get the same result? How can I be referencing two tables then? – discosammy Oct 13 '14 at 19:44
When you use the alias you are referring to the table that has this alias. If you say UPDATE st then there is no longer a cross product because you no longer introduce another table in the UPDATE clause. The cross product only manifests itself here if st has zero rows. In that case nothing will be updated. – usr Oct 13 '14 at 19:46
What does `UPDATE T1 SET ... FROM T2` do, in your mind? This is a valid statement. – usr Oct 13 '14 at 19:47

1 Answers1