Oracle MERGE statement ON clause error

Example code: **************************************

merge into genericitem x
using (select distinct changedocid, changedoctype from effectivity) y
on (x.itemid = y.changedocid and x.itemtypecd = y.changedoctype)
when matched then update set x.subtypecd = null
when not matched then insert (x.itemid, x.itemtypecd, x.subtypecd)
values (y.changedocid, y.changedoctype, null)

(itemid + itemtypecd) = the primaryKey for the genericitem table
(itemid + itemtypecd) = the primaryKey for the genericitem table

Problem: ******************************************

I get the Oracle error:
   ORA-00904: "X"."ITEMTYPECD": invalid identifier

generated from the clause:
    on (x.itemid = y.changedocid and x.itemtypecd = y.changedoctype)  

Question(s): ****************************************

1. Can I use multiple join operators?
2. If so, why am I getting the error?
3. I want to use this Merge statement for many tables that all require multiple join operators in the ON clause.  How can I make this work?

Thanks
MarkHensley
markjhensley@msn.com

MarkHensleyAsked:
Who is Participating?
 
geotigerConnect With a Mentor Commented:

Is itemtypecd somehow related to subtypecd (composite key from subtypecd or there is integrity constraint on it)?  You can not update key that is used in ON clause. This is an undocumented feature .

Please see it more at this link:

http://asktom.oracle.com/pls/ask/f?p=4950:8:6906283133203542202::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5318183934935,

GT

0
 
MarkHensleyAuthor Commented:
subtypecd is part of a UK on the genericitem table, the UK is defined as itemid,itemtypecd,subtypecd

There are only three columns in the entire table, so if I can't update any of the three then whant should the syntax of the following be?

when matched then update set x.subtypecd = null

When I try and onit the "when matched" clause it throws an error and there are no other columns to update.
0
 
geotigerCommented:
You might want to avoid MERGE INTO completely since you do not want to update it when matched. You then use the insert into as the following


Insert into genericitem x
select distinct changedocid, changedoctype, null
from effectivity
where x.itemid != changedocid and x.itemtypecd != changedoctype);

GT
0
 
geotigerCommented:
I do not know whether this is going to work. You can try it since you have the tables:

merge into genericitem x
using (select distinct changedocid, changedoctype from effectivity) y
on (x.itemid = y.changedocid and x.itemtypecd = y.changedoctype)
when matched then NULL
when not matched then insert (x.itemid, x.itemtypecd, x.subtypecd)
values (y.changedocid, y.changedoctype, null)
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.

All Courses

From novice to tech pro — start learning today.