如何避免合并时出现ORA-3814错误? [英] How to avoid ORA-3814 error on 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: Columns referenced in the ON clause cannot be updated.我了解此错误的原因.但是，我们如何才能重写此代码?不使用游标有没有可能?

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并因此失败，因此，如果您进入语句的WHEN NOT 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

这篇关于如何避免合并时出现ORA-3814错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！