跨数据库表更新报错：多部分标识符无法绑定（错误码4104）

错误原因 #

你遇到的4104错误和多条匹配记录无关，根本原因是在SET子句里用了原表全名，而非你在FROM中定义的别名。你已经给WENS.dbo.SUBSCRIPTION起了别名Table_A，给目标表WENS_IMPORT.dbo.new_import起了别名Table_B，SQL引擎只会识别这些别名，再用原表名引用字段就会出现绑定失败的错误。

如果是多条匹配的问题，错误提示会是类似“子查询返回多个值”这类，而非标识符绑定失败。

修正后的SQL语句 #

直接用别名来引用字段即可，修正后的代码如下：

UPDATE Table_B
SET
    Table_B.lat = Table_A.lat,
    Table_B.lon = Table_A.lon
FROM
    WENS.dbo.SUBSCRIPTION AS Table_A
INNER JOIN WENS_IMPORT.dbo.new_import AS Table_B
    ON Table_A.streetAddress = Table_B.Address
WHERE
    Table_A.account_id = '388' AND Table_A.active = '1'

也可以简化写法，省略SET里的表别名（因为UPDATE已经指定了目标表别名Table_B）：

UPDATE Table_B
SET
    lat = Table_A.lat,
    lon = Table_A.lon
FROM
    WENS.dbo.SUBSCRIPTION AS Table_A
INNER JOIN WENS_IMPORT.dbo.new_import AS Table_B
    ON Table_A.streetAddress = Table_B.Address
WHERE
    Table_A.account_id = '388' AND Table_A.active = '1'

额外提醒：多条匹配的处理 #

如果后续确实遇到同一个Address对应多条lat/lon记录的情况，SQL会随机选取一条记录的值更新目标表，这可能不符合预期。你可以先通过聚合或筛选确保每个地址只对应唯一的坐标，比如：

UPDATE Table_B
SET
    lat = agg.lat,
    lon = agg.lon
FROM
    (
        SELECT streetAddress, lat, lon
        FROM WENS.dbo.SUBSCRIPTION
        WHERE account_id = '388' AND active = '1'
        -- 根据实际需求添加唯一筛选条件，比如按id取最新记录
        GROUP BY streetAddress, lat, lon
    ) AS agg
INNER JOIN WENS_IMPORT.dbo.new_import AS Table_B
    ON agg.streetAddress = Table_B.Address

内容的提问来源于stack exchange，提问作者Scott