SQL Server下通过Select、Inner Join与Case When更新Claim表字段

SQL Server支持UPDATE语句直接关联多表，不需要嵌套子查询即可实现需求，完整实现代码如下：

UPDATE c
SET c.ClaimStatusID = CASE de.DamageApprovedStatusID
        WHEN -1 THEN 0
        WHEN 0 THEN 4
        WHEN 1 THEN 2
    END
FROM Claim c
-- 关联Damage表，Claim.ID与Damage.ID取值一致
INNER JOIN Damage d ON c.ID = d.ID
-- 关联中间表
INNER JOIN DamageErrandCrossDamage decd ON d.ID = decd.DamageID
-- 关联DamageErrand表取状态字段
INNER JOIN DamageErrand de ON decd.DamageErrandID = de.DamageErrandID

注意事项 #

• 执行更新前先执行下方验证语句，确认关联关系和映射结果符合预期，避免误更新数据：

SELECT 
    c.ID AS ClaimID,
    de.DamageApprovedStatusID,
    CASE de.DamageApprovedStatusID
        WHEN -1 THEN 0
        WHEN 0 THEN 4
        WHEN 1 THEN 2
    END AS TargetClaimStatusID
FROM Claim c
INNER JOIN Damage d ON c.ID = d.ID
INNER JOIN DamageErrandCrossDamage decd ON d.ID = decd.DamageID
INNER JOIN DamageErrand de ON decd.DamageErrandID = de.DamageErrandID

• 若单条Damage记录对应多条DamageErrand记录，需要先明确取值规则（比如取最新审核状态），先对DamageErrand侧数据做去重/聚合处理后再关联，避免重复关联导致更新结果异常
• 建议在事务中执行更新，确认结果正确后再提交，出错可回滚，事务示例如下：

BEGIN TRANSACTION

-- 放入上方的UPDATE更新语句
UPDATE c
SET c.ClaimStatusID = CASE de.DamageApprovedStatusID
        WHEN -1 THEN 0
        WHEN 0 THEN 4
        WHEN 1 THEN 2
    END
FROM Claim c
INNER JOIN Damage d ON c.ID = d.ID
INNER JOIN DamageErrandCrossDamage decd ON d.ID = decd.DamageID
INNER JOIN DamageErrand de ON decd.DamageErrandID = de.DamageErrandID

-- 验证更新结果，确认无误执行COMMIT TRANSACTION，出错执行ROLLBACK TRANSACTION
SELECT COUNT(*) FROM Claim WHERE ClaimStatusID IS NULL

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