Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 9i Merge Command Problem

Posted on 2004-08-10
5
Medium Priority
?
3,065 Views
Last Modified: 2012-05-05
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
Comment
Question by:Ktoshni
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 11770257
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
 
LVL 13

Expert Comment

by:riazpk
ID: 11770262
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
 
LVL 48

Expert Comment

by:schwertner
ID: 11770288
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
 
LVL 22

Expert Comment

by:earth man2
ID: 11770688
Your update statement is superfluous

dest.client_number is already equal to src.nclient
0
 

Author Comment

by:Ktoshni
ID: 11770796
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question