SQL MERGE INTO(尝试使用子查询插入数据库时​​出现问题)

Question

MERGE INTO DWCUST dc 
USING A2CUSTMELB cm
ON (dc.firstname=cm.fname AND  dc.surname=cm.sname and dc.postcode=cm.postcode)
WHEN MATCHED THEN UPDATE SET dc.DWSOURCEIDMELB=cm.custid
WHEN NOT MATCHED THEN 
INSERT (DWCUSTID, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
VALUES (DWCUST_SEQ.NEXTVAL, cm.custid, cm.fname, cm.sname, upper(cm.gender), cm.phone, cm.postcode, cm.city, cm.state,
(select cc.custcatname from a2custcategory cc  where  cm.custcatcode = cc.custcatcode ) );

错误报告-

SQL Error: ORA-00904: "CM"."CUSTCATCODE": invalid identifier

904. 00000 - "%s: invalid identifier"

如何解决这个错误？在我的 A2CUSTMELB 数据库中有一个名为 CUSTCATCODE 的列。

谢谢。

Answer 1

您不能将 SELECT 与 MERGE INSERT 一起使用，将连接移动到 USING:

MERGE INTO DWCUST dc 
USING
  (SELECT A2CUSTMELB.*, cc.custcatname 
   FROM A2CUSTMELB JOIN a2custcategory cc  
   ON CM.custcatcode = cc.custcatcode
  ) CM
ON (dc.firstname=CM.fname AND  dc.surname=CM.sname AND dc.postcode=CM.postcode)
WHEN MATCHED THEN UPDATE SET dc.DWSOURCEIDMELB=CM.custid
WHEN NOT MATCHED THEN 
INSERT (DWCUSTID, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
VALUES (DWCUST_SEQ.NEXTVAL, CM.custid, CM.fname, CM.sname, UPPER(CM.gender), CM.phone, CM.postcode, CM.city, CM.state,
        CM.custcatname );