Oracle MERGE引发ORA-00904错误 [英] Oracle MERGE raise ORA-00904 error
本文介绍了Oracle MERGE引发ORA-00904错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用merge命令将不存在的记录插入表中.当我使用简单的插入命令时,它可以正常工作.如果我使用合并系统,则始终警告ORA-00904:"T"."GROUP_COMPANY_ID"无效的标识符.一旦我将ON条件更改为(1 = 1)强制为true,那么merge命令就可以正常工作了.
I am using merge command to insert a non-exist record into table. When I use simple insert command it works fine. If I use merge system always alert ORA-00904: "T"."GROUP_COMPANY_ID" invalid identifier. Once I change the ON condition to (1=1) to force be true then the merge command works fine.
原始的合并语句出了什么问题?我非常确定该表的创建没有双引号,因此这里没有大小写问题.
What the original merge statement goes to wrong? I am very sure the table was created without double quoted name so there is not case issue here.
create table test
(
create_date DATE not null,
group_company_id CHAR(16) not null
)
-- This is okay
INSERT INTO test (create_date, group_company_id) VALUES (TO_DATE('20100531', 'YYYYMMDD'), 'abc');
-- This one will raise ORA-00904 error
MERGE INTO test T
USING (SELECT 'abc' AS group_company_id FROM DUAL) C
ON (T.group_company_id = C.group_company_id)
-- ON (1 = 1)
WHEN NOT MATCHED THEN
INSERT (create_date, group_company_id)
VALUES (TO_DATE('20100531', 'YYYYMMDD'), 'abc')
WHEN MATCHED THEN
UPDATE SET group_company_id = 'abc';
推荐答案
使用insert
和update
子句
WHEN NOT MATCHED THEN
INSERT (T.create_date, T.group_company_id)
VALUES (TO_DATE('20100531', 'YYYYMMDD'), 'abc')
WHEN MATCHED THEN
UPDATE SET T.group_company_id = 'abc';
MERGE INTO test
USING DUAL
ON (group_company_id = 'abc')
WHEN NOT MATCHED THEN
INSERT VALUES (TO_DATE('20100531', 'YYYYMMDD'), 'abc');
这篇关于Oracle MERGE引发ORA-00904错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文