SQL Server中高效实现指定行的硬编码数值乘法更新

问题场景 #

现有表MyTable的数据如下：

Code | Column1 | Column2
A01 | 20 | NULL
A02 | 50 | NULL
A03 | 10 | NULL
A04 | 20 | NULL
A05 | 30 | NULL

需要给Code为A02、A03、A04的行更新Column2，规则是Column2 = Column1 × 指定百分比：

• A02：乘20%
• A03：乘50%
• A04：乘100%

更新后预期结果：

Code | Column1 | Column2
A01 | 20 | NULL
A02 | 50 | 10
A03 | 10 | 5
A04 | 20 | 20
A05 | 30 | NULL

目前用多次UPDATE实现，每次改一个Code，不仅慢，以后加新规则还要再加新语句，太麻烦：

UPDATE A SET
Column2 = Column1*20/100 
FROM MyTable A
WHERE Code = 'A02'

UPDATE A SET
Column2 = Column1*50/100 
FROM MyTable A
WHERE Code = 'A03'

UPDATE A SET
Column2 = Column1*100/100 
FROM MyTable A
WHERE Code = 'A04'

更高效的实现方法 #

方法一：用CASE语句一次更完 #

把所有规则写到CASE里，一次UPDATE搞定，只扫一遍表，加新规则就加个WHEN分支就行：

UPDATE MyTable
SET Column2 = CASE Code
    WHEN 'A02' THEN Column1 * 20 / 100
    WHEN 'A03' THEN Column1 * 50 / 100
    WHEN 'A04' THEN Column1 * 100 / 100
    ELSE Column2 -- 其他行保持原有值
END
WHERE Code IN ('A02', 'A03', 'A04') -- 仅更新目标行，避免全表扫描

方法二：用JOIN关联规则表更新 #

如果规则多，或者以后要复用，先把规则做成临时表，再JOIN原表更新，规则看着更清楚，维护起来也方便：

UPDATE t
SET Column2 = t.Column1 * p.Percent / 100
FROM MyTable t
JOIN (
    VALUES
        ('A02', 20),
        ('A03', 50),
        ('A04', 100)
) AS p(Code, Percent) ON t.Code = p.Code

为啥这俩方法更好 #

• 都是一次UPDATE操作，比多次UPDATE少扫好几次表，速度更快
• 规则集中在一起，新增或修改规则不用加新的UPDATE语句，扩展性强
• 方法二还能把规则存成单独的配置表，以后改规则直接改配置表就行，不用动UPDATE语句

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