Oracle MERGE raise ORA-00904错误

Question

我正在使用merge命令将一个不存在的记录插入到表中。当我使用简单的insert命令时，它工作得很好。如果我使用合并系统，总是警告ORA-00904："T"."GROUP_COMPANY_ID“无效标识符。一旦我将ON条件更改为(1=1)以强制为真，那么merge命令就可以正常工作。

原始的merge语句出了什么问题？我非常确定创建该表时没有使用双引号名称，因此这里不存在大小写问题。

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';

Answer 1

使用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');

Answer 2

您不能更新正在加入的列。

Answer 3

您的代码不会抛出ORA-00904，它会抛出不言自明的ORA-38104

SQL> 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';
  2    3    4    5    6    7    8    9     ON (T.group_company_id = C.group_company_id)
       *
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated:
"T"."GROUP_COMPANY_ID"


SQL> 

我运行的是11gR2 --也许早期版本的行为有所不同。无论如何，解决方案非常简单:不要为匹配的分支而烦恼：

SQL> 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')
  2    3    4    5    6    7    8  /

0 rows merged.

SQL> 

当MERGE在9i中引入时，这种语法是无效的:我们必须包括两个分支。但它从10g开始就得到了支持。

如果您使用的是9i，因此需要匹配的分支，则必须更新joining子句中未包含的列。在您示例中应该是CREATE_DATE，