Oracle中使用CASE WHEN实现多表关联条件更新CASHDATE字段

正确的Oracle SQL实现方案 #

方案1：使用UPDATE + 关联子查询 + CASE WHEN实现

UPDATE TABLE1 t1
SET t1.CASHDATE = CASE
    -- 优先匹配TABLE2中ID='01'的记录
    WHEN EXISTS (SELECT 1 FROM TABLE2 t2 WHERE t2.NAME = t1.NAME AND t2.ID = '01')
        THEN TO_CHAR((SELECT t2.ID_DATE FROM TABLE2 t2 WHERE t2.NAME = t1.NAME AND t2.ID = '01'), 'YYYYMMDD')
    -- 无匹配时取TABLE3的SEC_DATE减1天
    ELSE TO_CHAR((SELECT t3.SEC_DATE - 1 FROM TABLE3 t3 WHERE t3.NAME = t1.NAME), 'YYYYMMDD')
END
WHERE t1.CASHDATE = '15990101';

方案2：使用Oracle MERGE语句实现（更推荐，性能更优）

MERGE INTO TABLE1 t1
USING (
    SELECT 
        t1_in.NAME,
        COALESCE(
            TO_CHAR(t2.ID_DATE, 'YYYYMMDD'),
            TO_CHAR(t3.SEC_DATE - 1, 'YYYYMMDD')
        ) AS NEW_CASHDATE
    FROM TABLE1 t1_in
    LEFT JOIN TABLE2 t2 ON t2.NAME = t1_in.NAME AND t2.ID = '01'
    LEFT JOIN TABLE3 t3 ON t3.NAME = t1_in.NAME
    WHERE t1_in.CASHDATE = '15990101'
) src
ON (t1.NAME = src.NAME)
WHEN MATCHED THEN UPDATE SET t1.CASHDATE = src.NEW_CASHDATE;

逻辑说明 #

• 两种写法均符合优先级规则：优先取TABLE2中同NAME且ID为01的ID_DATE，转成YYYYMMDD格式赋值；无匹配时自动取TABLE3同NAME的SEC_DATE减1天后转格式的结果
• 仅更新CASHDATE为默认值15990101的记录，不会影响其他已正常赋值的记录
• 执行后结果完全匹配预期：SAMY、TOMY取TABLE2的日期，DANY取TABLE3的2021-08-29减1天得到20210828

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