>I always thought that doing an update from a inner SQL, such as a massive update, was requiring the UPDATE line to use an alias. Basically, I always did something like this:
>
>
>UPDATE TemporaryName
>SET TemporaryName.MyField=5
>FROM ParentTable AS TemporaryName
>LEFT JOIN StatusTable ON TemporaryName.PrimaryKey=StatusTable.NoParentTable
>WHERE something
>
>
>But, if we do not use an alias, will this work as well:
>
>
>UPDATE ParentTable
>SET ParentTable.MyField=5
>FROM ParentTable AS TemporaryName
>LEFT JOIN StatusTable ON TemporaryName.PrimaryKey=StatusTable.NoParentTable
>WHERE something
>
>
>Because, when using an alias under SQL Anywhere, this does not work. In order to work with SQL Anywhere, we have to use the name of the table as is. Does this mean the alias usage is not SQL ANSI standard and that would be something that SQL Server would support by itself?
I suggest to keep using an alias, but you're right, both syntaxes work in SQL Server and it's a bit confusing. IMHO, using an alias is consistent.
BTW, many developers (say, MVP Hugo Kornelis) object using this syntax at all. Brad Schulz even has a blog 'Dear From Clause' on this exact topic.
-------------------------------------------------------
Dear FROM Clause,
I know that Valentine’s Day is less than a week away, but I felt it was important to write you this letter… I had to put my feelings down on paper. I don’t know quite how to tell you this, but… well… our relationship cannot continue.
------------------------------------------------------------------------------------------------------------------------
http://bradsruminations.blogspot.com/2010/02/dear-from-clause.html
If it's not broken, fix it until it is.
My Blog