SQL 什么是“多部分标识符”，为什么不能绑定？

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

提问by Even Mien

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:

SQL Server 2005 的具体错误是：

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.

消息 4104，级别 16，状态 1，第 3 行 无法绑定多部分标识符“dbBWKMigration.dbo.Company.COMPANYNAME”。

采纳答案by Whisk

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.

多部分标识符是对包含多个部分的字段或表的任何描述 - 例如 MyTable.SomeRow - 如果它不能被绑定，则意味着它有问题 - 你有一个简单的错字，或者两者之间的混淆表和列。它也可能是由于在表或字段名称中使用保留字而不是用 [] 包围它们引起的。这也可能是由于目标表中未包含所有必需的列而引起的。

Something like redgate sql promptis 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.

像redgate sql prompt这样的东西非常适合避免手动输入这些（它甚至可以根据外键自动完成连接），但不是免费的。SQL Server 2008 支持开箱即用的智能感知，尽管它不如 redgate 版本完整。

回答by amadelle

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 SomeFieldcolumn from Table1doesn't have the t1qualifier as t1.SomeFieldbut is just SomeField.

请注意，Table1中的SomeField列没有限定符 as而只是. t1t1.SomeFieldSomeField

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

如果尝试通过指定t1.SomeField语句来更新它，则会返回您注意到的多部分错误。

回答by Lieutenant Frost

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.

这可能是一个错字。在您的代码中查找您调用 [schema].[TableName] 的地方（基本上是您引用字段的任何地方），并确保所有内容都拼写正确。

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.

就我个人而言，我试图通过对所有表使用别名来避免这种情况。当您可以将长表名称缩短为其描述的首字母缩写词（即 WorkOrderParts -> WOP）时，它会非常有帮助，并且还使您的查询更具可读性。

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.

编辑：作为一个额外的好处，当您只需要输入三个或四个字母的别名与模式、表和字段名称时，您将节省大量的击键次数。

回答by Mark S. Rasmussen

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.

多部分标识符可以是：MyDatabase.dbo.MyTable。如果您弄错了这些标识符中的任何一个，那么您就有了一个无法映射的多部分标识符。

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.

避免它的最好方法是第一次就写出正确的查询，或者使用提供智能感知的管理工作室插件，从而通过避免拼写错误来帮助你。

回答by HLGEM

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.

你可能有一个错字。例如，如果您在名为 Sales 的数据库中有一个名为 Customer 的表，则可以将其称为 Sales..Customer（尽管最好将其包含所有者名称（dbo 是默认所有者）来引用，例如 Sales.dbo 。顾客。

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

如果您键入 Sales...Customer，您可能已经收到了您收到的消息。

回答by Pittsburgh DBA

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.

你使用什么排序规则？核实。

回答by jo-mso

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.

使查询工作而不抛出错误。

回答by Upio

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

我不得不删除 set 语句中的别名引用，所以它读起来像这样

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

回答by unnknown

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

我错误地将架构放在了 Alias 表上：

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

回答by Malhaar Punjabi

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

在我的情况下，在前面的 Set 字段中添加表别名会导致此问题。

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

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

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

错误更新 Table1 Set t1.SomeField = t2.SomeFieldValue From Table1 t1 Inner Join Table2 as t2 On t1.ID = t2.ID