单行列子查询返回多行问题：批量更新new_date字段报错求助

问题原因 #

你当前的UPDATE语句里，子查询没有和主表做任何关联，直接查询整个tw_edu_infra_amenities表会返回所有行的计算结果。而UPDATE操作要求给每一行的new_date赋值时，子查询必须仅返回对应行的单个值，因此触发了这个报错。

解决方案 #

方案1：直接使用当前行字段计算（推荐，更简洁高效）

不需要嵌套子查询，直接基于当前行的updated_date字段做逻辑判断，每一行独立计算对应的new_date值：

UPDATE tw_edu_infra_amenities
SET new_date = 
    CASE
        WHEN SUBSTR(TO_CHAR(TO_TIMESTAMP(updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD'), 1, 4) = '0021' THEN
            REPLACE(TO_CHAR(TO_TIMESTAMP(updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD'), '0021', '2021')
        ELSE TO_CHAR(TO_TIMESTAMP(updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD')
        -- 补充ELSE分支，避免非0021年份的行new_date被设为NULL
    END;

方案2：带关联条件的子查询（适用于必须用子查询的场景）

给主表和子表设置别名，通过表的唯一主键（比如id）关联，确保子查询仅返回当前行的计算结果：

UPDATE tw_edu_infra_amenities t1
SET new_date = 
    (SELECT CASE
                WHEN SUBSTR(TO_CHAR(TO_TIMESTAMP(t2.updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD'), 1, 4) = '0021' THEN
                    REPLACE(TO_CHAR(TO_TIMESTAMP(t2.updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD'), '0021', '2021')
                ELSE TO_CHAR(TO_TIMESTAMP(t2.updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD')
            END
     FROM tw_edu_infra_amenities t2
     WHERE t1.id = t2.id); -- 替换为你表的实际主键字段

优化建议：避免重复计算

如果表数据量较大，重复执行TO_TIMESTAMP和TO_CHAR会影响性能，可以用WITH子句提前处理格式化后的日期：

WITH processed_dates AS (
    SELECT 
        id, -- 替换为实际主键字段
        TO_CHAR(TO_TIMESTAMP(updated_date, 'DD-MM-YYYY HH12:MI:SS.FF AM'), 'YYYYMMDD') AS formatted_date
    FROM tw_edu_infra_amenities
)
UPDATE tw_edu_infra_amenities t1
SET new_date = 
    CASE
        WHEN SUBSTR(t2.formatted_date, 1, 4) = '0021' THEN
            REPLACE(t2.formatted_date, '0021', '2021')
        ELSE t2.formatted_date
    END
FROM processed_dates t2
WHERE t1.id = t2.id;

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