OraFAQ Forum: SQL & PL/SQL » Merge in Oracle V/S Merge in DB2

Home » SQL & PL/SQL » SQL & PL/SQL » Merge in Oracle V/S Merge in DB2 (Oracle 10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Merge in Oracle V/S Merge in DB2 [message #375934]

Mon, 15 December 2008 06:58

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

Report message to a moderator

Re: Merge in Oracle V/S Merge in DB2 [message #375938 is a reply to message #375934]

Mon, 15 December 2008 07:10

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

Report message to a moderator

Re: Merge in Oracle V/S Merge in DB2 [message #375944 is a reply to message #375938]

Mon, 15 December 2008 07:19

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:11:51 CST 2024