The multi-part identifier could not be bound

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

The multi-part identifier could not be bound

Author

Topic

exg001
Starting Member

4 Posts

Posted - 2010-09-23 : 11:01:05

We are switching servers, and upgrading our SQL, from 7.0 to 2005.

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 12:48:14

Have you run the upgrade advisor to detect problem areas?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exg001
Starting Member

4 Posts

Posted - 2010-09-23 : 14:16:05

Thanks for the reply Tara.

The upgrade advisor only gave me a few warnings about system tables not being used any more or about column level permission on system tables. The only warning it told me about on my tables is about using column aliases with a table alias prefix.

Ed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:20:19

I don't really have a good suggestion except to review and test each stored procedure for the incorrect syntax. Upgrading to a newer version of SQL Server should involve an entire regression effort. We spent months testing our system when we moved from 2000 to 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exg001
Starting Member

4 Posts

Posted - 2010-09-23 : 15:00:35

Thanks again Tara.

Every procedure is going to take more than months. But no choice is no choice :)

exg001
Starting Member

4 Posts

Posted - 2010-09-24 : 09:22:26

Ok, I found this out (reference: http://msdn.microsoft.com/en-us/library/ms178653(SQL.90).aspx)

If I have a compatibility level of 60 or 65, then:
"Columns prefixed with table aliases are accepted in the SET clause of an UPDATE statement."

Which is exactly what I want. It still doesn't work though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources