By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,492 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,492 IT Pros & Developers. It's quick & easy.

Problem with MERGE statement

P: 5
Hi All,

We are encountering a strange problem with the merge command.

The following statement works :-

merge into ATTRIBUTE_GROUP@US_PRODUCT_UAT a
using
(
select
a1.group_id,
a1.NAME,

a1.CREATE_DATE,
a1.MODIFY_DATE
from
ATTRIBUTE_GROUP_LOG a1,
product_push_wrk a2
where
a2.column_id = a1.group_id and
a2.modify_date = a1.modify_date ) b
on ( a.group_id = b.group_id)
when matched then
update set
a.NAME = b.NAME,
a.CREATE_DATE = b.CREATE_DATE,
a.MODIFY_DATE = b.MODIFY_DATE
when not matched then
insert
(
a.group_id,
a.NAME,
a.CREATE_DATE,
a.MODIFY_DATE
)
values
(
b.group_id,
b.NAME,
b.CREATE_DATE,
b.MODIFY_DATE
);


However when we change the order of the columns in the select query as follows the an error occurs : -

merge into ATTRIBUTE_GROUP@US_PRODUCT_UAT a
using
(
select
a1.NAME,
a1.group_id
,
a1.CREATE_DATE,
a1.MODIFY_DATE
from
ATTRIBUTE_GROUP_LOG a1,
product_push_wrk a2
where
a2.column_id = a1.group_id and
a2.modify_date = a1.modify_date ) b
on ( a.group_id = b.group_id)
when matched then
update set
a.NAME = b.NAME,
a.CREATE_DATE = b.CREATE_DATE,
a.MODIFY_DATE = b.MODIFY_DATE
when not matched then
insert
(
a.group_id,
a.NAME,
a.CREATE_DATE,
a.MODIFY_DATE
)
values
(
b.group_id,
b.NAME,
b.CREATE_DATE,
b.MODIFY_DATE
);

ERROR at line 15:
ORA-00904: "B"."GROUP_ID": invalid identifier

SQL> l 15
15* on ( a.group_id = b.group_id)


The structure of the attribute_log table is as follows :-

SQL> desc ATTRIBUTE_GROUP
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
GROUP_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(96)
CREATE_DATE NOT NULL DATE
MODIFY_DATE NOT NULL DATE


Any pointers to the cause of this error will be highly appreciated.

Thanks and Regards,
Suman
May 2 '07 #1
Share this Question
Share on Google+
1 Reply


debasisdas
Expert 5K+
P: 8,127
Can u please send the structure of the second table involved in the MERGE
(product_push_wrk )
May 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.