Re: MERGE - INSERT/UPDATE

poratips shah via DBMonster.com wrote:

> 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

Where is the primary key coming from? If a sequence then your insert should contain sequence_name.NEXTVAL. If another source it is up to you to provide it in your insert statement or with a BEFORE-INSERT trigger.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)