Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084942474.929841_at_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?
No WHERE clause is required in the UPDATE part of a MERGE statement -
the ON clause does that - so that isn't Rishi's problem. I don't know
what is though.
Received on Wed May 19 2004 - 06:38:08 CDT
