[Webinar] Streamline your web hosting managementRegister Today

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

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

0
MarkHensley
Asked:
MarkHensley
  • 3
1 Solution
 
geotigerCommented:

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

Featured Post

Get your problem seen by more experts

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

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