Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3079
  • Last Modified:

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
0
Ktoshni
Asked:
Ktoshni
Advertise Here
1 Solution
 
schwertnerCommented:
The cause is that you cannot update a column that has been referenced in the ON condition clause.


Restrictions on the update clause:  
   - You cannot specify DEFAULT when updating a view.  
   - You cannot update a column that has been referenced in the ON condition clause.
   - You cannot update the same row of the target table multiple times in the  same MERGE statement.
 
Prerequisites:
   - You must have INSERT and UPDATE object privileges on the target table  
     and SELECT privilege on the source table.
0
 
riazpkCommented:
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  /


0
 
schwertnerCommented:
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.
0
 
earth man2Commented:
Your update statement is superfluous

dest.client_number is already equal to src.nclient
0
 
KtoshniAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now