asked on

Oracle 9i Merge Command Problem

Hi I'm trying to use the merge command but have encountered a problem which does not seem to make sense. I am running the following code:

merge into dest
using src
on (dest.client_number = src.nclient)
when matched then
update set
dest.client_number = src.nclient
when not matched then
insert (dest.client_number)
values
(src.nclient)

when trying to run it I get the following error:

ORA-00904: "DEST"."CLIENT_NUMBER": invalid identifier

Now this table exists and the user I am using is the owner of the table so I can't understand what's wrong. Any ideas?

The version of Oracle I am using is 9.2.0.1.0.

Thanks

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ora_Techie

It appears to be an undocumented restriction that you cannot UPDATE any column
you are MERGING on.

Here is example from http://asktom.oracle.com/pls/ask/f?p=4950:8:12923460400259688987::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5318183934935,

MERGE INTO newemp n1
USING external_table e1
ON ( n1.empno = e1.empno )
WHEN MATCHED THEN UPDATE
SET n1.empno = e1.empno,
n1.ename = e1.ename,
n1.job = e1.job,
n1.mgr = e1.mgr,
n1.hiredate = e1.hiredate,
n1.sal = e1.sal,
n1.comm = e1.comm,
n1.deptno = e1.deptno
WHEN NOT MATCHED THEN
INSERT ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e1.empno,
e1.ename,
e1.job,
e1.mgr,
e1.hiredate,
e1.sal,
e1.comm,
e1.deptno )
/

ON ( n1.empno = e1.empno )
*
ERROR at line 3:
ORA-00904: "N1"."EMPNO": invalid identifier

ops$tkyte@ORA920.US.ORACLE.COM> MERGE INTO newemp n1
2 USING external_table e1
3 ON ( n1.empno = e1.empno )
4 WHEN MATCHED THEN UPDATE
5 SET -- n1.empno = e1.empno,
6 n1.ename = e1.ename,
7 n1.job = e1.job,
8 n1.mgr = e1.mgr,
9 n1.hiredate = e1.hiredate,
10 n1.sal = e1.sal,
11 n1.comm = e1.comm,
12 n1.deptno = e1.deptno
13 WHEN NOT MATCHED THEN
14 INSERT ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
15 values ( e1.empno,
16 e1.ename,
17 e1.job,
18 e1.mgr,
19 e1.hiredate,
20 e1.sal,
21 e1.comm,
22 e1.deptno )
23 /

schwertner

This is well documented prerequisite.
Also in 9.1 there is a bug, but it is known and you also use 9.2. So I think that we encounter the cause.

earth man2

Your update statement is superfluous

dest.client_number is already equal to src.nclient

Ktoshni

ASKER

Thanks guys for the help! I'm awarding the points to schwertner since he pointed out the problem first but thanks to all for the unput.