PL/SQL 12C中MERGE INTO更新报错及UPDATE关联表问题求助

错误原因 #

Oracle MERGE语法存在限制：ON子句中引用的目标表列，不允许在UPDATE分支中被修改。你的语句里，ON子句通过iu.LOGIN匹配users表，同时UPDATE分支要修改iu.LOGIN，这会破坏匹配逻辑，因此触发错误。

解决方案 #

调整MERGE逻辑，先在USING子句中筛选出所有需要更新的目标行（结合WHERE条件和users表关联），用rowid作为唯一匹配标识，避免在ON子句中引用要修改的iu.LOGIN列：

MERGE INTO ident_utilisateur iu
USING (
    SELECT 
        iu.rowid AS rid,
        regexp_substr(iu.LOGIN, '[^_]+', 1) AS login_prefix
    FROM ident_utilisateur iu
    JOIN users us 
        ON regexp_substr(iu.LOGIN, '[^_]+', 1) = TO_CHAR(us.cnbf)
    WHERE iu.login LIKE '%_LOG_%' 
      AND iu.login NOT LIKE '%AUT_LOG%'
) src
ON (iu.rowid = src.rid)
WHEN MATCHED THEN 
UPDATE
SET
    iu.login = src.login_prefix,
    iu.email_contact = regexp_substr(iu.email_contact, '[^_]+', 1),
    iu.valide = 1,
    iu.date_fin = ''

错误原因 #

Oracle不支持MySQL/SQL Server风格的UPDATE ... FROM ... JOIN ...语法，这种写法不符合Oracle SQL规范。

解决方案 #

方式1：使用WHERE EXISTS子句关联users表 #

UPDATE ident_utilisateur iu
SET
    iu.login = regexp_substr(iu.login, '[^_]+', 1),
    iu.email_contact = regexp_substr(iu.email_contact, '[^_]+', 1),
    iu.valide = 1,
    iu.date_fin = ''
WHERE iu.login LIKE '%_LOG_%'
  AND iu.login NOT LIKE '%AUT_LOG%'
  AND EXISTS (
      SELECT 1
      FROM users us
      WHERE regexp_substr(iu.login, '[^_]+', 1) = TO_CHAR(us.CNBF)
  )

方式2：复用问题1的MERGE语句 #

MERGE本身就可以实现带关联的UPDATE逻辑，且语法符合Oracle规范，是更通用的解决方案。

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