SQL UPDATE语句无法更新3a字段为1a与2a之和问题求助

错误原因 #

你的SQL语句中，子查询里错误添加了a2.question_name = a.question_name条件。主查询的a对应的是question_name LIKE '3a.%'的行，这会让子查询试图寻找同时满足question_name LIKE '1a.%'和question_name = '3a.xxx'的记录——显然不存在这类数据，所以子查询返回NULL，最终3a行的字段值仍为NULL。

修正后的SQL方案 #

方案1：修正子查询条件

直接去掉子查询中错误的a2.question_name = a.question_name条件：

UPDATE acrl.ACRLData_Edited_Test a
SET question_textvalue = 
    CAST(
        (SELECT question_textvalue 
         FROM acrl.ACRLData_Edited_Test a2 
         WHERE a2.ipeds_id = a.ipeds_id
             AND a2.question_name LIKE '1a.%'
        ) AS DECIMAL(10,2))
    + CAST(
        (SELECT question_textvalue 
         FROM acrl.ACRLData_Edited_Test a2 
         WHERE a2.ipeds_id = a.ipeds_id
             AND a2.question_name LIKE '2a.%'
        ) AS DECIMAL(10,2))
WHERE a.question_name LIKE '3a.%'

方案2：处理NULL值（避免无匹配记录时结果为NULL）

如果存在1a或2a记录缺失的情况，用ISNULL将NULL替换为0，确保计算结果不为空：

UPDATE acrl.ACRLData_Edited_Test a
SET question_textvalue = 
    ISNULL(CAST(
        (SELECT question_textvalue 
         FROM acrl.ACRLData_Edited_Test a2 
         WHERE a2.ipeds_id = a.ipeds_id
             AND a2.question_name LIKE '1a.%'
        ) AS DECIMAL(10,2)), 0)
    + ISNULL(CAST(
        (SELECT question_textvalue 
         FROM acrl.ACRLData_Edited_Test a2 
         WHERE a2.ipeds_id = a.ipeds_id
             AND a2.question_name LIKE '2a.%'
        ) AS DECIMAL(10,2)), 0)
WHERE a.question_name LIKE '3a.%'

方案3：使用JOIN提升效率（推荐）

用LEFT JOIN一次性关联1a和2a的数据，逻辑更清晰且性能更优：

UPDATE a3
SET question_textvalue = ISNULL(CAST(a1.question_textvalue AS DECIMAL(10,2)), 0) + ISNULL(CAST(a2.question_textvalue AS DECIMAL(10,2)), 0)
FROM acrl.ACRLData_Edited_Test a3
LEFT JOIN acrl.ACRLData_Edited_Test a1 
    ON a1.ipeds_id = a3.ipeds_id AND a1.question_name LIKE '1a.%'
LEFT JOIN acrl.ACRLData_Edited_Test a2 
    ON a2.ipeds_id = a3.ipeds_id AND a2.question_name LIKE '2a.%'
WHERE a3.question_name LIKE '3a.%'

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