sql update table set - 无法绑定多部分标识符

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/14741859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 13:32:11  来源:igfitidea点击:

sql update table set - The multi-part identifier could not be bound

sqlsql-serversql-update

提问by codingknob

I have 2 tables:

我有2张桌子:

  • Table1= names of gas stations (in pairs)
  • Table2= has co-ordinate information (longitude and latitude amongst other things)
  • 表 1= 加油站名称(成对)
  • 表 2= 具有坐标信息(经度和纬度等)

Example of Table1:

表 1示例:

StationID1  StationID2  Name1   Name2   Lattitude1  Longitude1  Lattitude2  Longitude2  Distance
------------------------------------------------------------------------------------------------
93353477    52452   FOO     BAR     NULL        NULL        NULL    NULL    NULL
93353527    52452   HENRY   BENNY   NULL        NULL        NULL    NULL    NULL
93353551    52452   GALE    SAM     NULL        NULL        NULL    NULL    NULL

Example of Table2:

2示例:

IDInfo     Name  Lattitude    Longitude
-------------------------------------------
93353477   BAR   37.929654    -87.029622

I want to update this table with the coordinate information which resides in tableA. I tried to do the following as per SQL Server 2005: The multi-part identifier … could not be bound

我想用位于tableA. 我尝试按照SQL Server 2005执行以下操作:多部分标识符……无法绑定

update table1
set t1.[Lattitude1] = t2.[Lattitude]
from table1 t1
left join table2 t2 
on (t1.StationID1 = t2.IDInfo)

I get the following error message:

我收到以下错误消息:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t1.Lattitude1" could not be bound.

消息 4104,级别 16,状态 1,第 1 行
无法绑定多部分标识符“t1.Lattitude1”。

However, if I do the following it works which I can then store into another table.

但是,如果我执行以下操作,则它可以工作,然后我可以将其存储到另一个表中。

SELECT t1.[StationID1]
      ,t1.[StationID2]
      ,t1.[Name1]
      ,t1.[Name2]
        ,t2.[Lattitude] AS [Lattitude1]
        ,t2.[Longitude] AS [Longitude1]
        ,t3.[Lattitude] AS [Lattitude2]
        ,t3.[Longitude] AS [Longitude2]
from table1 t1
left join table2 t2 
on (t1.StationID1 = t2.IDInfo)
left join table2 t3 
on (t1.StationID2 = t2.IDInfo)

I am very new to SQL and am having a difficult time understanding why some things work and others don't. Based on the link I posted above my initial query should have worked - no? Perhaps I'm not thinking straight as I have spent many hours trying this and I finally got help from a co-worker (she suggested the approach I mention above).

我对 SQL 非常陌生,并且很难理解为什么有些事情有效而其他事情却没有。根据我在上面发布的链接,我的初始查询应该有效 - 不是吗?也许我没有直接思考,因为我花了很多时间尝试这个,我终于得到了一位同事的帮助(她建议了我上面提到的方法)。

回答by bobs

I think you can modify your UPDATE statement to reference the table alias in the UPDATE line.

我认为您可以修改 UPDATE 语句以引用 UPDATE 行中的表别名。

update t1
set t1.[Lattitude1] = t2.[Lattitude]
from table1 t1
left join table2 t2 
on (t1.StationID1 = t2.IDInfo)

回答by cgatian

You need to change the inner table and give a different allias to the columns that are similar. This should work.

您需要更改内表并为相似的列指定不同的别名。这应该有效。

update table1
set [Lattitude1] = x.[lat]
from 
(
    SELECT IDInfo [id], Lattitude [lat] FROM 
    table2
) x
WHERE
StationID1 = x.[id]

In your particular case its not necessary to rename Lattitude to lat, but if you end up updating a table with itself and force yourself into giving the columns different names, it will save you headaches down the road.

在您的特定情况下,没有必要将 Lattitude 重命名为 lat,但是如果您最终使用自身更新表格并强迫自己为列指定不同的名称,那么它将为您省去麻烦。