What is a 'multi-part identifier' and why can't it be bound?

Question

I continually get these errors when I try to update tables based on another table. I end up rewriting the query, change the order of joins, change some groupings and then it eventually works, but I just don't quite get it.

What is a 'multi-part identifier'?
When is a 'multi-part identifier' not able to be bound?
What is it being bound to anyway?
In what cases will this error occur?
What are the best ways to prevent it?

The specific error from SQL Server 2005 is:

The multi-part identifier "..." could not be bound.

Here is an example:

SELECT * FROM [MainDB].[dbo].[Company] 
WHERE [MainDB].[dbo].[Company].[CompanyName] = 'StackOverflow'

The actual error:

Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "MainDB.dbo.Company.CompanyName" could not be bound.

Answer 1

What I was making mistake that I did not write the query for JOIN and I was using one of the columns of the alias of that supposed table in the SELECT clause.

Before:

select t2.FirstName from table1 t1

After

select t2.FirstName from table1 t1 left join table2 t2 --missing this one. on t1.ID = t2.ID

Answer 2

I was using an alias but that alias I was not using in select

It is important to use an alias when data is coming from another database and use that alias with your select statement with fields

Example Database_Name.dbo.Table_Name as Alias_Name with(NOLOCK) ON Join Condition

in Select Statement Alias_Name.Columns_Name

Answer 3

CTRL+SHIFT+R (refreshing the Intellisense) took care of it for me.

Answer 4

Mine worked after removing square brackets in a SUBSTRING method. I changed from

SUBSTRING([dbo.table].[column],15,2)

to

SUBSTRING(dbo.table.column,15,2)

Answer 5

I had exactly the same issue, and similar to your coding I had missed out the FROM field, once it is added, the query knows what table to read the data from

Answer 6

Adding table alias in front Set field causes this problem in my case.

Right

Update Table1
Set SomeField = t2.SomeFieldValue 
From Table1 t1 
Inner Join Table2 as t2
    On t1.ID = t2.ID

Wrong

Update Table1
Set t1.SomeField = t2.SomeFieldValue 
From Table1 t1 
Inner Join Table2 as t2
    On t1.ID = t2.ID

Answer 7

I faced this problem and solved it but there is a difference between your and mine code. In spite of I think you can understand what is "the multi-part identifier could not be bound"

When I used this code

 select * from tbTest where email = sakira@gmail.com

I faced Multi-part identifier problem

but when I use single quotation for email address It solved

 select * from tbTest where email = 'sakira@gmail.com'

Answer 8

A multipart identifier is any description of a field or table that contains multiple parts - for instance MyTable.SomeRow - if it can't be bound that means there's something wrong with it - either you've got a simple typo, or a confusion between table and column. It can also be caused by using reserved words in your table or field names and not surrounding them with []. It can also be caused by not including all of the required columns in the target table.

Something like redgate sql prompt is brilliant for avoiding having to manually type these (it even auto-completes joins based on foreign keys), but isn't free. SQL server 2008 supports intellisense out of the box, although it isn't quite as complete as the redgate version.

Answer 9

When you type the FROM table those errors will disappear. Type FROM below what your typing then Intellisense will work and multi-part identifier will work.

Answer 10

I was getting this error and just could not see where the problem was. I double checked all of my aliases and syntax and nothing looked out of place. The query was similar to ones I write all the time.

I decided to just re-write the query (I originally had copied it from a report .rdl file) below, over again, and it ran fine. Looking at the queries now, they look the same to me, but my re-written one works.

Just wanted to say that it might be worth a shot if nothing else works.

Answer 11

My best advise when having the error is to use [] braquets to sorround table names, the abbreviation of tables causes sometimes errors, (sometime table abbreviations just work fine...weird)

Answer 12

Error Code

FROM                
    dbo.Category C LEFT OUTER JOIN           
    dbo.SubCategory SC ON C.categoryID = SC.CategoryID AND C.IsActive = 'True' LEFT OUTER JOIN          
    dbo.Module M ON SC.subCategoryID = M.subCategoryID AND SC.IsActive = 'True' LEFT OUTER JOIN          
    dbo.SubModule SM ON M.ModuleID = SM.ModuleID AND M.IsActive = 'True' AND SM.IsActive = 'True' LEFT OUTER JOIN 
    dbo.trainer ON dbo.trainer.TopicID =dbo.SubModule.subModuleID 

Solution Code

 FROM                
    dbo.Category C LEFT OUTER JOIN           
    dbo.SubCategory SC ON C.categoryID = SC.CategoryID AND C.IsActive = 'True' LEFT OUTER JOIN          
    dbo.Module M ON SC.subCategoryID = M.subCategoryID AND SC.IsActive = 'True' LEFT OUTER JOIN          
    dbo.SubModule SM ON M.ModuleID = SM.ModuleID AND M.IsActive = 'True' AND SM.IsActive = 'True' LEFT OUTER JOIN 
    dbo.trainer ON dbo.trainer.TopicID = SM.subModuleID 

as you can see, in error code, dbo.SubModule is already defined as SM, but I am using dbo.SubModule in next line, hence there was an error. use declared name instead of actual name. Problem solved.

Answer 13

Actually sometimes when you are updating one table from another table's data, I think one of the common issues that cause this error, is when you use your table abbreviations incorrectly or when they are not needed. The correct statement is below:

Update Table1
Set SomeField = t2.SomeFieldValue 
From Table1 t1 
Inner Join Table2 as t2
    On t1.ID = t2.ID

Notice that SomeField column from Table1 doesn't have the t1 qualifier as t1.SomeField but is just SomeField.

If one tries to update it by specifying t1.SomeField the statement will return the multi-part error that you have noticed.

Answer 14

I actually forgot to join the table to the others that's why i got the error

Supposed to be this way:

  CREATE VIEW reserved_passangers AS
  SELECT dbo.Passenger.PassName, dbo.Passenger.Address1, dbo.Passenger.Phone
  FROM dbo.Passenger, dbo.Reservation, dbo.Flight
  WHERE (dbo.Passenger.PassNum = dbo.Reservation.PassNum) and
  (dbo.Reservation.Flightdate = 'January 15 2004' and Flight.FlightNum =562)

And not this way:

  CREATE VIEW reserved_passangers AS
  SELECT dbo.Passenger.PassName, dbo.Passenger.Address1, dbo.Passenger.Phone
  FROM dbo.Passenger, dbo.Reservation
  WHERE (dbo.Passenger.PassNum = dbo.Reservation.PassNum) and
  (dbo.Reservation.Flightdate = 'January 15 2004' and Flight.FlightNum = 562)

Answer 15

I had P.PayeeName AS 'Payer' --, and the two comment lines threw this error

Answer 16

You probably have a typo. For instance, if you have a table named Customer in a database named Sales, you could refer to it as Sales..Customer (although it is better to refer to it including the owner name (dbo is the default owner) like Sales.dbo.Customer.

If you typed Sales...Customer, you might have gotten the message you got.

Answer 17

Mine was putting the schema on the table Alias by mistake:

SELECT * FROM schema.CustomerOrders co
WHERE schema.co.ID = 1  -- oops!

Answer 18

I found that I get these a lot when I try to abbreviate, such as:

Table1 t1, Table2 t2 
where t1.ID = t2.ID

Changing it to:

Table1, Table2 
where Table1.ID = Table2.ID

Makes the query work and not throw the error.

Answer 19

When updating tables make sure you do not reference the field your updating via the alias.

I just had the error with the following code

update [page] 
set p.pagestatusid = 1
from [page] p
join seed s on s.seedid = p.seedid
where s.providercode = 'agd'
and p.pagestatusid = 0

I had to remove the alias reference in the set statement so it reads like this

update [page] 
set pagestatusid = 1
from [page] p
join seed s on s.seedid = p.seedid
where s.providercode = 'agd'
and p.pagestatusid = 0

Answer 20

I had this issue and it turned out to be an incorrect table alias. Correcting this resolved the issue.

Answer 21

If you are sure that it is not a typo spelling-wise, perhaps it is a typo case-wise.

What collation are you using? Check it.

Answer 22

It's probably a typo. Look for the places in your code where you call [schema].[TableName] (basically anywhere you reference a field) and make sure everything is spelled correctly.

Personally, I try to avoid this by using aliases for all my tables. It helps tremendously when you can shorten a long table name to an acronym of it's description (i.e. WorkOrderParts -> WOP), and also makes your query more readable.

Edit: As an added bonus, you'll save TONS of keystrokes when all you have to type is a three or four-letter alias vs. the schema, table, and field names all together.

Answer 23

Binding = your textual representation of a specific column gets mapped to a physical column in some table, in some database, on some server.

Multipart identifier could be: MyDatabase.dbo.MyTable. If you get any of these identifiers wrong, then you have a multipart identifier that cannot be mapped.

The best way to avoid it is to write the query right the first time, or use a plugin for management studio that provides intellisense and thus help you out by avoiding typos.