PLSQL中UPDATE语句使用JOIN关联更新报错，如何调整语句实现需求

你原写法存在两个核心问题：

• 流程控制语法IF ... THEN ... END IF仅能在存储过程/触发器的逻辑块中使用，单条UPDATE语句的赋值逻辑需改用CASE表达式实现分支判断
• 不同数据库对多表关联UPDATE的语法支持存在差异，以下为三种主流数据库的可执行写法：

MySQL 版本 #

UPDATE subject_detail_overall sdo
INNER JOIN subject_detail_personal sdp 
ON sdo.subject_id = sdp.subject_id
SET sdo.name_lessformal = CASE
    WHEN sdp.sex = 1 THEN CONCAT(
        SUBSTR(sdo.name_lessformal, 1, 7),
        UPPER(SUBSTR(sdo.name_lessformal, 8, 1)),
        SUBSTR(sdo.name_lessformal, 9)
    )
    WHEN sdp.sex = 2 THEN CONCAT(
        SUBSTR(sdo.name_lessformal, 1, 5),
        UPPER(SUBSTR(sdo.name_lessformal, 6, 1)),
        SUBSTR(sdo.name_lessformal, 7)
    )
    ELSE sdo.name_lessformal
END
WHERE sdp.sex IN (1,2)
AND (
    (sdp.name_birthnameprefix IS NOT NULL AND sdp.useofname IN (2,3))
    OR 
    (sdp.name_partnernameprefix IS NOT NULL AND sdp.useofname IN (1,4))
);

注意MySQL默认模式下||是逻辑或运算符，字符串拼接需使用CONCAT()函数。

PostgreSQL 版本 #

UPDATE subject_detail_overall sdo
SET name_lessformal = CASE
    WHEN sdp.sex = 1 THEN SUBSTR(sdo.name_lessformal, 1, 7) || UPPER(SUBSTR(sdo.name_lessformal, 8, 1)) || SUBSTR(sdo.name_lessformal, 9)
    WHEN sdp.sex = 2 THEN SUBSTR(sdo.name_lessformal, 1, 5) || UPPER(SUBSTR(sdo.name_lessformal, 6, 1)) || SUBSTR(sdo.name_lessformal, 7)
    ELSE sdo.name_lessformal
END
FROM subject_detail_personal sdp
WHERE sdo.subject_id = sdp.subject_id
AND sdp.sex IN (1,2)
AND (
    (sdp.name_birthnameprefix IS NOT NULL AND sdp.useofname IN (2,3))
    OR 
    (sdp.name_partnernameprefix IS NOT NULL AND sdp.useofname IN (1,4))
);

PostgreSQL的多表关联UPDATE需使用FROM子句引入关联表，关联条件写入WHERE块。

Oracle 版本 #

UPDATE (
    SELECT 
        sdo.name_lessformal,
        sdp.sex
    FROM subject_detail_overall sdo
    INNER JOIN subject_detail_personal sdp 
    ON sdo.subject_id = sdp.subject_id
    WHERE sdp.sex IN (1,2)
    AND (
        (sdp.name_birthnameprefix IS NOT NULL AND sdp.useofname IN (2,3))
        OR 
        (sdp.name_partnernameprefix IS NOT NULL AND sdp.useofname IN (1,4))
    )
) t
SET t.name_lessformal = CASE
    WHEN t.sex = 1 THEN SUBSTR(t.name_lessformal, 1, 7) || UPPER(SUBSTR(t.name_lessformal, 8, 1)) || SUBSTR(t.name_lessformal, 9)
    WHEN t.sex = 2 THEN SUBSTR(t.name_lessformal, 1, 5) || UPPER(SUBSTR(t.name_lessformal, 6, 1)) || SUBSTR(t.name_lessformal, 7)
    ELSE t.name_lessformal
END;

Oracle需先将关联结果封装为可更新视图，再对视图字段进行赋值，要求关联的subject_id字段需为主键或有唯一约束保证视图可更新。

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