Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problems with MERGE Statement

Re: Problems with MERGE Statement

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 18 May 2004 21:54:31 -0700
Message-ID: <1084942474.929841@yasure>


rishi wrote:

> 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
> '||attrib_id||' Value '||UTCDate);
>
> 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 315

Take a good look at your update statement:

UPDATE SET ATTRIBUTE_VALUE=UTCDate, INSERT_DATETIME=SYSDATE, INSERT_PROCESS='ETL', UPDATE_DATETIME=SYSDATE, UPDATE_PROCESS='ETL' Do you see a WHERE clause?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue May 18 2004 - 23:54:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US