MERGE - INSERT/UPDATE

From: poratips shah via DBMonster.com <forum_at_nospam.DBMonster.com>
Date: Tue, 03 May 2005 16:36:12 GMT
Message-ID: <b7b2db316d99437abc2e7b16f9b8aac3@DBMonster.com>

Hi,
I am getting following errors while using MERGE. ORA-02291: integrity constraint (PTEST.SYS_C0010720) violated - parent key not found
I am i using following MERGE sql.
Sample MERGE sql for one ATTRIBUTE

---

MERGE.sql

---

BEGIN

          MERGE INTO SKU_ATTR T
             USING (select sku_id,'BuyEP', EMP_FLAG from SKU_ATTR_UPLOAD) S
                  ON    (S.SKU_ID = T.SKU_ID and T.ATTRIBUTE_NAME = 'BuyEP')
       WHEN MATCHED THEN
       UPDATE
          SET       T.ATTRIBUTE_VALUE	=  DECODE


(S.EMP_FLAG,'','NULL',S.EMP_FLAG)

      WHEN NOT MATCHED THEN
      INSERT 

( T.SKU_ID,T.ATTRIBUTE_NAME,T.ATTRIBUTE_VALUE)
VALUES
 ( CASE etl.merge_counter(etl.c_inserting) WHEN 0 THEN S.SKU_ID END, 'BuyEP',DECODE(S.EMP_FLAG,'','NULL',S.EMP_FLAG) );

      DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_insert_count) || ' rows
inserted.');
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_update_count( SQL%ROWCOUNT
)) || ' rows updated.');
      etl.reset_counters;

--commit;

   END;
 /

How can use the MERGE or another techniques to INSERT the records if it's not there or if it's there and any changes I can update the records?

Thanks in advance!
Poratips Received on Tue May 03 2005 - 11:36:12 CDT