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:

UPDATE  [test].[dbo].[CompanyDetail]

SET Mnemonic = [dbBWKMigration].[dbo].[Company].[MNEMONIC], 
               [Company Code] = [dbBWKMigration].[dbo].[Company].[COMPANYCODE]

WHERE [Company Name] = **[dbBWKMigration].[dbo].[Company].[COMPANYNAME]**

The actual error:

Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "dbBWKMigration.dbo.Company.COMPANYNAME" could not be bound.

Answer 1

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 2

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 3

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 4

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.

Answer 5

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 6

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 7

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 8

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 9

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 10

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

Answer 11

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

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

Answer 12

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

Answer 13

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 14

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 15

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 16

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 17

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 18

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 = [email protected]

I faced Multi-part identifier problem

but when I use single quotation for email address It solved

 select * from tbTest where email = '[email protected]'