473,324 Members | 2,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Problem with MERGE statement

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
1 2270
debasisdas
8,127 Expert 4TB
Can u please send the structure of the second table involved in the MERGE
(product_push_wrk )
May 2 '07 #2

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

Similar topics

2
by: Ray | last post by:
I have a list of about 20,000 rows that I am updating. I loop through each row in my program and basically do the following (1) select * from TABLE where SID= for update /*lock the row*/ (2)...
1
by: Bob Stearns | last post by:
Does the following message get generated before or after the additional conditions given in the merge are evaluated? Error: The same row of target table "NULLID.AICA" was identified more than...
1
by: rebeaj | last post by:
Merge SQL statement will not compile. Can anyone help me out? Create Or Replace Procedure P_pvt_merge As Begin Merge Into Employees2 B Using ( Select Employee_id, First_name, Last_name,...
4
by: marklawford | last post by:
Hi guys, I'm getting the following error message when trying to run a MERGE statement I'm putting together. The syntax looks right to me But i must be missing something. The "srce" table...
16
by: Sam Durai | last post by:
Hello, I need to merge a small table (of rows less than 100,sometimes even 0 rows) to a big table (of rows around 4 billion). I used the PK of the big table as merge key but merge does a table scan...
5
by: melentina | last post by:
Hello, I'm trying to use MERGE statement in a procedure, please see the code below: procedure gen_nou (p_data date) is v_atribut_id number; begin v_atribut_id=100;
1
by: UDBDBA | last post by:
Hi, If someone can clarify this, it would be of great help! We have a merge, which is written *only* to update the target table. but the access plan shows branches with inserts and deletes on...
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
7
by: Henry J. | last post by:
I got a dumb question on the merge statement. I read the following example of merge statement at the IBM page:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.