这个语句错在哪里？

显示全部楼层 · 2010-1-4 08:33:08

merge into copy_emp c USING employees e
on ( c.EMPLOYEE_ID = e.employee_id)
when matched then
updateset
c.employee_id=e.employee_id,
c.first_name=e.first_name,
c.last_name= e.last_name,
c.email= e.email,
c.phone_number=e.phone_number,
c.hire_date=e.hire_date,
c.job_id=e.job_id,
c.salary=e.salary,
c.commission_pct=e.commission_pct,
c.manager_id=e.manager_id,
c.department_id=e.department_id
when not matched then
insert values(e.employee_id,
e.first_name,e.last_name,e.email,e.phone_number,
e.hire_date,e.job_id,e.salary,e.commission_pct,
e.manager_id,e.department_id);
on ( c.EMPLOYEE_ID = e.employee_id)
*
ERROR at line 2:
ORA-00904: "C"."EMPLOYEE_ID": invalid identifier
谢谢！

千问 · 2010-1-4 08:33:08

不用别名用表全名试试?

千问 · 2010-1-4 08:33:08

也试过了不行！

千问 · 2010-1-4 08:33:08

merge into copy_empUSING employees
on ( copy_emp.EMPLOYEE_ID = employees.employee_id)
when matched then
updateset
copy_emp.employee_id=employees.employee_id,
copy_emp.first_name=employees.first_name,
copy_emp.last_name= employees.last_name,
copy_emp.email= employees.email,
copy_emp.phone_number=employees.phone_number,
copy_emp.hire_date=employees.hire_date,
copy_emp.job_id=employees.job_id,
copy_emp.salary=employees.salary,
copy_emp.commission_pct=employees.commission_pct,
copy_emp.manager_id=employees.manager_id,
copy_emp.department_id=employees.department_id
when not matched then
insert values(employees.employee_id,employees.first_name,
employees.last_name,employees.email,employees.phone_number,
employees.hire_date,employees.job_id,employees.salary,
employees.commission_pct,employees.manager_id,
employees.department_id);
on ( copy_emp.EMPLOYEE_ID = employees.employee_id)
*
ERROR at line 2:
ORA-00904: "COPY_EMP"."EMPLOYEE_ID": invalid identifier

千问 · 2010-1-4 08:33:08

when not matched then insert (字段) values(字段试试);
感觉你不是就一个单独merge吧，如果是嵌在一个procedure里边，很可能是前边的有问题。

千问 · 2010-1-4 08:33:08

是单个merge，这是SG上的SAMPLE，但我就执行不成功！不知道哪里有问题。

千问 · 2010-1-4 08:33:08

噢,版本是什么?

千问 · 2010-1-4 08:33:08

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

千问 · 2010-1-4 08:33:08

UP！

千问 · 2010-1-4 08:33:08

9i新增了一個DML語句：MERGE，語法為：
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN UPDATE SET col1 = col_val1,col2 = col2_val
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);
根据條件，如果記錄存在，則使用UPDATE，否則使用INSERT。
9i的關聯查詢，可以使用
CROSS JOIN、NATURAL JOIN、JOIN...USING...、JOIN...ON...、
LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN