如何编写SQL覆写表数据？基于匹配条件与时间区间的更新问询

嘿，我来帮你搞定这个问题！根据你描述的场景——在其余字段匹配且Var5处于指定时间区间时，用Name2的Var1和Var2值替换所有Name1行——我整理了两种实用方案，你可以根据实际需求选择：

1. 查询时返回覆写结果（不修改原表） #

如果只是想在查询输出中看到替换后的效果，不想改动原表的数据，用CASE语句就能轻松实现。这里分两种场景：

场景A：动态匹配Name2的字段（推荐，适配数据变化） #

如果Name2的Var2/Var3/Var4可能变动，用关联子查询动态匹配：

SELECT
    CASE
        WHEN t.Var1 = 'Name1' 
             AND t.Var5 BETWEEN '201805' AND '202001'
             AND EXISTS (SELECT 1 FROM myTable t2 
                         WHERE t2.Var1 = 'Name2' 
                         AND t2.Var2 = t.Var2 
                         AND t2.Var3 = t.Var3 
                         AND t2.Var4 = t.Var4)
        THEN 'Name2'
        ELSE t.Var1
    END AS Var1,
    CASE
        WHEN t.Var1 = 'Name1' 
             AND t.Var5 BETWEEN '201805' AND '202001'
             AND EXISTS (SELECT 1 FROM myTable t2 
                         WHERE t2.Var1 = 'Name2' 
                         AND t2.Var2 = t.Var2 
                         AND t2.Var3 = t.Var3 
                         AND t2.Var4 = t.Var4)
        THEN (SELECT t2.Var2 FROM myTable t2 
              WHERE t2.Var1 = 'Name2' 
              AND t2.Var2 = t.Var2 
              AND t2.Var3 = t.Var3 
              AND t2.Var4 = t.Var4)
        ELSE t.Var2
    END AS Var2,
    t.Var3,
    t.Var4,
    t.Var5
FROM myTable t;

场景B：固定Name2的字段值（适合数据固定的情况） #

如果Name2的Var2/Var3/Var4是固定值（比如你示例里的2.1、2.2、0），可以直接写死条件，简化查询：

SELECT
    CASE
        WHEN Var1 = 'Name1' 
             AND Var5 BETWEEN '201805' AND '202001'
             AND Var2 = 1.1 AND Var3 = 1.2 AND Var4 = 0
        THEN 'Name2'
        ELSE Var1
    END AS Var1,
    CASE
        WHEN Var1 = 'Name1' 
             AND Var5 BETWEEN '201805' AND '202001'
             AND Var2 = 1.1 AND Var3 = 1.2 AND Var4 = 0
        THEN 2.1
        ELSE Var2
    END AS Var2,
    Var3,
    Var4,
    Var5
FROM myTable;

2. 永久更新表中的数据（修改原表） #

如果需要把替换后的结果永久保存到表中，就用UPDATE语句。同样分两种情况：

动态匹配版 #

UPDATE myTable t
SET 
    Var1 = 'Name2',
    Var2 = (SELECT t2.Var2 FROM myTable t2 
            WHERE t2.Var1 = 'Name2' 
            AND t2.Var2 = t.Var2 
            AND t2.Var3 = t.Var3 
            AND t2.Var4 = t.Var4)
WHERE 
    t.Var1 = 'Name1'
    AND t.Var5 BETWEEN '201805' AND '202001'
    AND EXISTS (SELECT 1 FROM myTable t2 
                WHERE t2.Var1 = 'Name2' 
                AND t2.Var2 = t.Var2 
                AND t2.Var3 = t.Var3 
                AND t2.Var4 = t.Var4);

固定值简化版 #

UPDATE myTable
SET 
    Var1 = 'Name2',
    Var2 = 2.1
WHERE 
    Var1 = 'Name1'
    AND Var5 BETWEEN '201805' AND '202001'
    AND Var2 = 1.1 AND Var3 = 1.2 AND Var4 = 0;

几个重要提醒 #

• 注意Var5的类型：如果是日期类型，要调整区间格式（比如改成BETWEEN '2018-05-01' AND '2020-01-31'）；如果是字符串/数字，当前写法没问题。
• 避免多值冲突：如果Name2有多行符合匹配条件，子查询可能返回多个值，这时候可以加LIMIT 1或者用MAX()/MIN()这类聚合函数确保只取一个值。
• 先验证再更新：执行UPDATE前，建议先用对应的SELECT语句跑一遍，确认筛选出来的行是你要修改的，防止误操作！

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