Problem With MERGE in SQL
535608Nov 3 2006 — edited Nov 3 2006I have created a dummy table for employees with the name copy_emp and I am trying to use MERGE statement with employees as source and copy_emp as target. I have used the following query
MERGE INTO copy_emp c
USING employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE set 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 );
When I am executing the above statement in TOAD I am getting the following error:
ORA-02012: missing USING keyword
When I am executing the above statement in SQL*PLUS or ISQL*PLUS the following error occured:
USING employees e ON (c.employee_id = e.employee_id)
*
ERROR at line 2:
ORA-00904: "C"."EMPLOYEE_ID": invalid identifier
I have used the following statement to create the copy_emp table
create table copy_emp as select * from employees where employee_id<>employee_id;
So it has created an empty table with the same structure as of employees.
Can anyone help me with this....
Thanks