SQL合并为(使用子查询插入数据库时出现问题)

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

Error report -

错误报告,

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

SQL错误:ora - 00904:“厘米”。”CUSTCATCODE”:无效的标识符

904. 00000 - "%s: invalid identifier"
905. 00000 -“%s:无效标识符”

How can I fix this error? There is a column called CUSTCATCODE in my A2CUSTMELB database.

我如何修正这个错误?在我的A2CUSTMELB数据库中有一个名为CUSTCATCODE的列。

Thanks.

谢谢。

1 个解决方案

#1

You can't use a SELECT with a MERGE INSERT, move the join into the USING:

不能使用带有合并插入的SELECT语句，将连接移动到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 );