SQL 合并时出现 ORA-38104 错误的原因是什么? [英] What is cause of ORA-38104 error on SQL merge?

问题描述

我有一个这样的代码

MERGE INTO target_table tgt
USING source_table src
on(tgt.c1=src.c1)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

我收到 ORA-38104:无法更新 ON 子句中引用的列.我了解此错误的原因.但是我们如何重写这段代码呢?有没有不使用游标的可能性?

I get ORA-38104: Columns referenced in the ON clause cannot be updated. I understand the reason for this error. But how can we rewrite this code? Is there any possibilities without using cursor?

推荐答案

这个怎么样，外连接意味着 rid 将为 null 从而失败，所以流入 WHENNOT MATCHED 声明的一部分，如果你有的话

How about this, the outer join means the rid will be null and thus fail, and so flow into the WHEN NOT MATCHED part of the statement if you have one

MERGE INTO target_table tgt
USING ( SELECT t2.ROWID AS rid
            ,  s2.c2
        FROM   target_table t2
             , source_table s2
        WHERE t2.c1 (+) = s2.c1
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

这篇关于SQL 合并时出现 ORA-38104 错误的原因是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！