跨MS SQL数据库更新销售表经销商ID遇子查询报错求助

你遇到的报错核心原因有两个：

1. 原SQL的子查询没有和db2.salestable做关联，导致它返回所有满足名称匹配的DB2经销商ID列表，而非当前要更新的单条销售记录对应的ID。
2. 如果DB2的dealer表中存在同名但不同ID的经销商记录，子查询会返回多行结果，直接触发"子查询返回多个值"的报错。

下面是几种可行的解决写法：

方案1：使用UPDATE JOIN（推荐，高效且逻辑清晰） #

这种方式通过表关联直接匹配对应关系，从根源避免子查询返回多行的问题。

场景1：DB2销售表需关联DB1销售表匹配经销商 #

如果需要通过DB1销售表的记录来定位DB2销售表的更新目标（比如通过订单号、销售ID等关联字段）：

UPDATE db2_sales
SET db2_sales.dealer = db2_dealer.Id
FROM db2.dbo.salestable db2_sales
-- 关联DB1销售表，找到对应经销商的名称来源
JOIN db1.dbo.sales db1_sales ON db2_sales.[订单号/销售ID等关联字段] = db1_sales.[对应关联字段]
-- 通过DB1销售表的经销商ID，找到DB1经销商的名称
JOIN db1.dbo.Dealer db1_dealer ON db1_sales.dealer = db1_dealer.Id
-- 通过名称匹配DB2经销商的ID
JOIN db2.dbo.dealer db2_dealer ON db1_dealer.Name = db2_dealer.Name
-- 可选：只更新DB2销售表中无经销商ID的记录
WHERE db2_sales.dealer IS NULL;

场景2：直接替换DB2销售表的经销商ID #

如果DB2销售表已有旧的经销商ID（对应DB1的经销商ID），需要直接替换为DB2自身的经销商ID：

UPDATE db2_sales
SET db2_sales.dealer = db2_dealer.Id
FROM db2.dbo.salestable db2_sales
-- 通过旧ID找到DB1经销商的名称
JOIN db1.dbo.Dealer db1_dealer ON db2_sales.dealer = db1_dealer.Id
-- 匹配DB2经销商的ID
JOIN db2.dbo.dealer db2_dealer ON db1_dealer.Name = db2_dealer.Name;

方案2：先清理DB2经销商表的重复名称 #

如果DB2的dealer表中确实存在同名的经销商记录（业务上不允许的情况），需要先清理重复数据：

1. 先查询重复的经销商名称：

SELECT Name, COUNT(*) AS 重复记录数
FROM db2.dbo.dealer
GROUP BY Name
HAVING COUNT(*) > 1;

2. 清理重复数据（示例：保留ID最大的记录，删除其他）：

DELETE FROM db2.dbo.dealer
WHERE Id NOT IN (
    SELECT MAX(Id)
    FROM db2.dbo.dealer
    GROUP BY Name
);

清理完成后，再使用方案1的UPDATE语句即可。

方案3：使用WITH子句实现关联映射 #

如果你坚持要用WITH子句，可以预先定义DB1与DB2经销商的ID映射关系，再执行更新：

WITH 经销商ID映射 AS (
    SELECT 
        db1_dealer.Id AS DB1经销商ID,
        db2_dealer.Id AS DB2经销商ID
    FROM db1.dbo.Dealer db1_dealer
    JOIN db2.dbo.dealer db2_dealer ON db1_dealer.Name = db2_dealer.Name
)
UPDATE db2_sales
SET db2_sales.dealer = 经销商ID映射.DB2经销商ID
FROM db2.dbo.salestable db2_sales
JOIN db1.dbo.sales db1_sales ON db2_sales.[关联字段] = db1_sales.[关联字段]
JOIN 经销商ID映射 ON db1_sales.dealer = 经销商ID映射.DB1经销商ID
WHERE db2_sales.dealer IS NULL;

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