Aliases in Joined Updates. 1

[travisbrown]

Anyone have an idea why I'm getting Incorrect syntax near 'o' on the following query for SQL2005? Doesn't seem to actually be an aliasing problem; I get a similar error if I just use the table names.

UPDATE dbo.sub_orders o INNER JOIN dbo.TBL_REGION r ON r.region_name = o.billing_province
SET o.billing_province = r.region_code

 

[gmmastros]

You need a FROM clause when using multiple table, like this:

UPDATE o 
SET    o.billing_province = r.region_code
From   dbo.sub_orders As o
       INNER JOIN dbo.TBL_REGION r 
         ON r.region_name = o.billing_province

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[travisbrown]

Ah...yes, I recall now. Thanks George.

Was it like this in SQL 2000 as well? I seem to recall always using my original approach.

Curiously, this Tek-Tips tip suggest the same method as mine:

http://www.tek-tips.com/faqs.cfm?fid=1958

 

[markros]

FROM clause in UPDATE is not ANSI standard. MVP Brad Schulz and Hugo Kornelis both written blogs on this topic

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

PluralSight Learning Library