September 23, 2010 at 1:47 pm
We are switching servers, and upgrading our SQL, from 7.0 to 2005. I'm having a problem with table aliases, so I have setup a test to illustrate.
I have two trial tables:
CREATE TABLE [trial1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [trial2] (
[data1] [int] NULL ,
[data2] [int] NULL
) ON [PRIMARY]
GO
I want to run the following update:
update trial1
set t1.col2 = t2.data2
from trial2 t2 , trial1 t1
where t1.col1 = t2.data1
On the SQL Server 7.0, it works fine.
On 2005 I get the following error:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t1.col2" could not be bound.
If I take out the t1 alias on the set statement or change the "update trial1" to "update t1", it works fine.
Both of these work on 2005:
update trial1
set col2 = t2.data2
from trial2 t2 , trial1 t1
where t1.col1 = t2.data1
OR
update t1
set t1.col2 = t2.data2
from trial2 t2 , trial1 t1
where t1.col1 = t2.data1
Any suggestions, as I'm sure I have a ton of procedures doing it this way?
September 23, 2010 at 2:26 pm
From http://msdn.microsoft.com/en-us/library/ms178653(SQL.90).aspx
Compatibility level setting of 60 or 65:
Columns prefixed with table aliases are accepted in the SET clause of an UPDATE statement.
Compatibility level setting of 70 or higher:
Table aliases are not accepted in the SET clause of an UPDATE statement. The table or view specified in the SET clause must match that specified immediately following the UPDATE keyword. For more information, see UPDATE (Transact-SQL).
What was the compatibility level of your DB in SQL 7.0? Was it 60 or 65?
September 24, 2010 at 6:23 am
Compatibility level in 7.0 was 65 and it is now 65 in 2005.
I've read through the link you sent me, and it was good. I tried several of the differences described, and most work just as written (ltrim(' ') does indeed give different results for different compatibility levels).
However, the one that I am having an issue with does NOT work as stated 🙁 My level is 65, but I still get an error for using an alias in a set clause of an update statement.
September 24, 2010 at 1:02 pm
Maybe this will fix it?
September 24, 2010 at 2:03 pm
winash comes through! This worked like a charm. Thank you very much, I couldn't find that anywhere.
Thanks again.
winash (9/24/2010)
Maybe this will fix it?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply