sql update table set - 无法绑定多部分标识符 [英] sql update table set - The multi-part identifier could not be bound
问题描述
我有 2 张桌子:
- 表 1 = 加油站名称(成对)
- Table2 = 包含坐标信息(经度和纬度等)
- Table1 = names of gas stations (in pairs)
- Table2 = has co-ordinate information (longitude and latitude amongst other things)
表 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
Table2 示例:
IDInfo Name Lattitude Longitude
-------------------------------------------
93353477 BAR 37.929654 -87.029622
我想用 tableA
中的坐标信息更新这个表.我尝试按照 SQL Server 执行以下操作2005:多部分标识符……无法绑定
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
update table1
set t1.[Lattitude1] = t2.[Lattitude]
from table1 t1
left join table2 t2
on (t1.StationID1 = t2.IDInfo)
我收到以下错误消息:
消息 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)
我对 SQL 非常陌生,我很难理解为什么有些事情有效而其他事情却没有.根据我在上面发布的链接,我的初始查询应该有效 - 不是吗?也许我没有认真思考,因为我花了很多时间尝试这个,我终于得到了一位同事的帮助(她建议了我上面提到的方法).
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).
推荐答案
我认为您可以修改 UPDATE 语句以引用 UPDATE 行中的表别名.
I think you can modify your UPDATE statement to reference the table alias in the UPDATE line.
update t1
set t1.[Lattitude1] = t2.[Lattitude]
from table1 t1
left join table2 t2
on (t1.StationID1 = t2.IDInfo)
这篇关于sql update table set - 无法绑定多部分标识符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!