我正在尝试在Oracle 11g中执行以下代码,并遇到错误。
MERGE INTO EMP_COPY4 C
USING (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMAIL, E.COMMISSION_PCT FROM EMPLOYEES) E
ON (C.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHEN MATCHED THEN
UPDATE SET
C.FIRST_NAME = E.FIRST_NAME,
C.LAST_NAME = E.LAST_NAME,
C.EMAIL = E.EMAIL,
C.COMMISSION_PCT = E.COMMISSION_PCT
DELETE WHERE (E.COMMISSION_PCT IS NOT NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMAIL, E.COMMISSION_PCT);
EMP_COPY4是EMPLOYEES的副本表,仅具有以下列EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,COMMISSION_PCT
我想在某些情况下合并它们,但Oracle给出了以下错误
Error starting at line : 405 in command -
MERGE INTO EMP_COPY4 C
USING (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMAIL, E.COMMISSION_PCT FROM EMPLOYEES) E
ON (C.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHEN MATCHED THEN
UPDATE SET
C.FIRST_NAME = E.FIRST_NAME,
C.LAST_NAME = E.LAST_NAME,
C.EMAIL = E.EMAIL,
C.COMMISSION_PCT = E.COMMISSION_PCT
DELETE WHERE (E.COMMISSION_PCT IS NOT NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMAIL, E.COMMISSION_PCT)
Error at Command Line : 406 Column : 66
Error report -
SQL Error: ORA-00904: "E"."COMMISSION_PCT": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
第405行是语句的开始,第406行是第二行。
您能帮我弄清楚为什么会出现这种错误吗...?
用任一替换二维线
USING (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, COMMISSION_PCT FROM EMPLOYEES) E
或者
USING (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMAIL, E.COMMISSION_PCT FROM EMPLOYEES E) E
如果表没有别名,则不能在SELECT列表中使用别名
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句