Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Problems with MERGE Statement
Has anyone had any problems with the MERGE statement inserting
incorrect values. Below is a piece of my stored procedure
DBMS_OUTPUT.PUT_LINE('1 CustomerID '||cust_id||' Atrribute_id
'||attrib_id||' Value '||UTCDate);
MERGE INTO cust_attributes_old ca
USING (SELECT cust_id customer_id, attrib_id attribute_id FROM DUAL) ca1
ON (ca.customer_id = ca1.customer_id and ca.attribute_id = ca1.attribute_id)
WHEN MATCHED THEN UPDATE SET ATTRIBUTE_VALUE=UTCDate, INSERT_DATETIME=SYSDATE, INSERT_PROCESS='ETL', UPDATE_DATETIME=SYSDATE, UPDATE_PROCESS='ETL' WHEN NOT MATCHED THEN INSERT (CUSTOMER_ID,ATTRIBUTE_ID,ATTRIBUTE_VALUE,INSERT_DATETIME,INSERT_PROCESS,UPDATE_DATETIME,UPDATE_PROCESS) VALUES (cust_id,attrib_id,UTCDate,SYSDATE,'ETL',SYSDATE,'ETL');insert into temp_hold values (cust_id,attrib_id,UTCDate); DBMS_OUTPUT.PUT_LINE('2 CustomerID '||cust_id||' Atrribute_id
In this case cust_id = 16721069, attrib_id = 315 and attribute_value = 0434. After running this script the values that get populated into cust_Attributes_old is cust_id = 434, attrib_id = 315 and attribute_value = 315. However the insert into the temp_hold table (for debugging) is what I would expect...below are the results.
exec ETL_CUSTATTRIB_STGTOTRG_ALT2v2
1 CustomerID 16721069 Atrribute_id 315 Value 0434
2 CustomerID 16721069 Atrribute_id 315 Value 0434
SQL> select * from temp_hold;
CUSTOMER_ID ATTRIBUTE_ID ATTRIBUTE_VALUE
----------- ------------ ------------------ 16721069 315 0434
SQL> select * from cust_attributes_old;
CUSTOMER_ID ATTRIBUTE_ID ATTRIBUTE_VALUE
----------- ------------ ---------------- 434 315 315Received on Tue May 18 2004 - 11:26:36 CDT
![]() |
![]() |