T-SQL双表联动更新运费脚本IF条件返回值过多如何修正

• 原有脚本IF判断的子查询返回多值结果集：SELECT [Shippers number] FROM Freightview会返回全表所有运单编号，无法直接用=做等值对比，T-SQL的IF仅支持对标量（单个值）做判断，因此触发返回值过多的报错
• 全局判断逻辑不符合需求：你需要按每个匹配的sonum维度分别判断是否存在运费行，而非全表统一走更新/插入分支，不需要外层全局IF控制

-- 第一步：更新已存在的运费行tcost
UPDATE T 
SET tcost = FR.[Invoice Amount]
FROM TSH1 T 
INNER JOIN Freightview FR 
    ON FR.[Shippers number] = T.sonum
WHERE T.productnum IN ('FRT-OUT', 'FRT-IN', 'FRT')

-- 第二步：插入不存在运费行的新记录
INSERT INTO TSH1 (sonum, productnum, tcost, productid, pd, qtyfulfilled, tprice, stdcost, flag, avgcost)
SELECT 
    FR.[Shippers number] AS sonum,
    'FRT' AS productnum,
    FR.[Invoice Amount] AS tcost,
    '7240' AS productid,
    'FREIGHT' AS pd,
    1 AS qtyfulfilled,
    0 AS tprice,
    0 AS stdcost,
    'D' AS flag,
    NULL AS avgcost
FROM Freightview FR
-- 匹配到TSH1中存在对应sonum
WHERE EXISTS (SELECT 1 FROM TSH1 T WHERE T.sonum = FR.[Shippers number])
-- 且该sonum下没有三类运费行
AND NOT EXISTS (
    SELECT 1 FROM TSH1 T 
    WHERE T.sonum = FR.[Shippers number]
    AND T.productnum IN ('FRT-OUT', 'FRT-IN', 'FRT')
)
-- 避免同一个sonum重复插入
AND NOT EXISTS (
    SELECT 1 FROM TSH1 T 
    WHERE T.sonum = FR.[Shippers number] AND T.productnum = 'FRT'
)

-- 第三步：返回TSH1存在但Freightview不存在的不匹配数据
SELECT * 
FROM TSH1 T 
LEFT JOIN Freightview FR 
    ON T.sonum = FR.[Shippers number]
WHERE FR.[Shippers number] IS NULL

-- 第四步：返回Freightview存在但TSH1不存在的不匹配数据
SELECT * 
FROM Freightview FR 
LEFT JOIN TSH1 T 
    ON T.sonum = FR.[Shippers number]
WHERE T.sonum IS NULL

• 移除了全局IF判断，改为按单条运单维度分别处理更新和插入逻辑，从根源避免多值判断报错
• 修正了原脚本中金额赋值颠倒的错误：原临时表更新逻辑[Invoice Amount] = TT.tcost逻辑错误，调整为运费行成本直接取Freightview的发票金额
• 插入逻辑直接指定字段写入，避免SELECT *导致的表结构变化后字段不匹配问题，也省去了临时表处理、去重的冗余步骤
• 不匹配查询独立执行，不需要放在ELSE分支中，确保每次运行都能返回全量不匹配数据

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