Home » SQL & PL/SQL » SQL & PL/SQL » Merge in Oracle V/S Merge in DB2 (Oracle 10.2.0.4)
Merge in Oracle V/S Merge in DB2 [message #375934] Mon, 15 December 2008 06:58 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Could some one help me out with this?
MERGE INTO ASN_DETAIL ADTL 
  USING
  (
   SELECT AD.ASN_ID,AD.ASN_DETAIL_ID,PORD.PURCHASE_ORDERS_ID FROM PURCHASE_ORDERS PORD,
  (
   SELECT AD.ASN_ID,AD.ASN_DETAIL_ID,AD.TC_PURCHASE_ORDERS_ID,ICBO.COMPANY_ID 
   FROM ASN_DETAIL AD, ITEM_CBO ICBO WHERE ICBO.ITEM_ID = AD.SKU_ID
  ) AD
   WHERE PORD.TC_PURCHASE_ORDERS_ID = AD.TC_PURCHASE_ORDERS_ID AND PORD.TC_COMPANY_ID = AD.COMPANY_ID
  ) PO
  ON (PO.ASN_DETAIL_ID = ADTL.ASN_DETAIL_ID AND PO.ASN_ID = ADTL.ASN_ID)
  WHEN MATCHED THEN
  UPDATE SET PURCHASE_ORDERS_ID = PO.PURCHASE_ORDERS_ID
ELSE IGNORE;


I have to convert the above code (db2) to Oracle.

Reading up led me to some material which explained the syntax as exactly the same, except for the ELSE IGNORE syntax.

Is there some way I could use the same ELSE IGNORE or the same functionality in Oracle?

If not matched, I wish to ignore the rest of the rows and do nothing.

Thanks,

Sharath
Re: Merge in Oracle V/S Merge in DB2 [message #375938 is a reply to message #375934] Mon, 15 December 2008 07:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

What is the use of else ignore ?
If the rows isn't a match they will not be updated..


Why are you using Merge ?

You have some good examples here link

And Oracle documentation
Re: Merge in Oracle V/S Merge in DB2 [message #375944 is a reply to message #375938] Mon, 15 December 2008 07:19 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I found out the alternative. The query can be written like this

MERGE INTO ASN_DETAIL ADTL 
  USING
  (
   SELECT AD.ASN_ID,AD.ASN_DETAIL_ID,PORD.PURCHASE_ORDERS_ID FROM PURCHASE_ORDERS PORD,
  (
   SELECT AD.ASN_ID,AD.ASN_DETAIL_ID,AD.TC_PURCHASE_ORDERS_ID,ICBO.COMPANY_ID 
   FROM ASN_DETAIL AD, ITEM_CBO ICBO WHERE ICBO.ITEM_ID = AD.SKU_ID
  ) AD
   WHERE PORD.TC_PURCHASE_ORDERS_ID = AD.TC_PURCHASE_ORDERS_ID AND PORD.TC_COMPANY_ID = AD.COMPANY_ID
  ) PO
  ON (PO.ASN_DETAIL_ID = ADTL.ASN_DETAIL_ID AND PO.ASN_ID = ADTL.ASN_ID)
  WHEN MATCHED THEN
  UPDATE SET PURCHASE_ORDERS_ID = PO.PURCHASE_ORDERS_ID;


The WHEN NOT MATCHED clause is not mandatory.

Previous Topic: alter character set at session level
Next Topic: substr prob
Goto Forum:
  


Current Time: Tue Dec 06 16:32:19 CST 2016

Total time taken to generate the page: 0.09027 seconds