[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3072
  • 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
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now