merge statement err

Question

I have created two tables with following descriptions

SQL> desc new_emp Name Null? Type

---

EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

SQL> desc old_emp Name Null? Type

---

EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

I m using an Merge statment as given below for these two tables

Merge into new_emp n using old_emp o on (o.empno=n.empno) when matched then update set n.empno=o.empno, n.ename=o. ename, n.job=o.job, n.hiredate=o.hiredate, n.sal=o.sal, n.comm=o.comm, n.deptno=o.deptno
when not matched then insert values( o.empno, o. ename, o.job, o.hiredate, o.sal, o.comm, o.deptno )

when I executed the above statement system shows following error, i m using ORACLE 9i version

ORA-00904: "N"."EMPNO": invalid identifier

Answer 1

In Oracle 10.2.0.1.0 I get this error with your MERGE statement:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "N"."EMPNO"

So try removing "n.empno=o.empno" from your SET clause.