oracle 如何避免合并时出现 ORA-3814 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7886905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid ORA-3814 error on merge?
提问by Vivek
I have a code like this
我有这样的代码
MERGE INTO target_table tgt
USING source_table src
on(tgt.c1=src.c1)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2
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?
我明白了ORA-38104: Columns referenced in the ON clause cannot be updated。我了解此错误的原因。但是我们如何重写这段代码呢？有没有不使用游标的可能性？
回答by Sodved
How about this, the outer join means the ridwill be null and thus fail, and so flow into the WHEN NOT MATCHEDpart of the statement if you have one
这个怎么样，外连接意味着rid将是空的，因此失败，所以WHEN NOT MATCHED如果你有的话，流入语句的一部分
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
回答by Lukas Eder
You can exploit some workarounds for ORA-38104, which seem to work up until Oracle 18c, including this one, where you'd wrap your columns in a row value expression that contains an additional dummy expression:
您可以利用 ORA-38104 的一些变通方法，这些变通方法似乎在 Oracle 18c 之前都有效，包括这个，您可以将列包装在包含附加虚拟表达式的行值表达式中：
MERGE INTO target_table tgt
USING source_table src
ON ((tgt.c1, 'dummy') = ((src.c1, 'dummy')))
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2
