Home » SQL & PL/SQL » SQL & PL/SQL » Merge with extra condition (Oracle 9i)
Merge with extra condition [message #346390] Mon, 08 September 2008 07:18 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

Is it possible to put an extra condition in merge statement when the condition falls into 'when not matched then' mode.

I have created the following merge statement.
 
merge into xml_1 t using  app_contact_xml_1 o
on (t.app_name=o.app_name)
when matched then 
update set t.APP_TYPE=o.APP_TYPE,
t.APP_OWNER_NAME=(select L1_NAME||';'||L2_NAME||';'||L3_NAME from  app_contact_xml_1 
where   APP_NAME=o.app_name) 
when not matched then
--Here i want to put some extra condition(if t.aap_name is not null then execute the insert statement)
insert(t.APP_NAME,t.APP_TYPE,t.APP_OWNER_NAME) 
values(o.APP_NAME,o.APP_TYPE,o.L1_NAME||';'||o.L2_NAME||';'||o.L3_NAME)


Regards,
Pointers.

[Updated on: Mon, 08 September 2008 07:55] by Moderator

Report message to a moderator

Re: Merge with extra condition [message #346400 is a reply to message #346390] Mon, 08 September 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do it wrong.

1/ in "matched" part, you have not to select the values are already in "o".

2/ in "not matched" part, "t.aap_name" does not exist as you are currently inserting the row, so your comment is wrong.

Regards
Michel
Re: Merge with extra condition [message #346402 is a reply to message #346390] Mon, 08 September 2008 07:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I haven't tried it myself, but try the conditional_insert_clause of the multi_table_insert form of an insert statement.
Re: Merge with extra condition [message #346410 is a reply to message #346390] Mon, 08 September 2008 08:14 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Sorry Michel, I dint get your first point..
for second point, I am sorry it was a typo. It is actually app_name.
I am just concatinating source table different column values and updating for a single column in the destination table.

But, It is required to put some extra condition when not matched part. I tried by doing


merge into xml_1 t using  app_contact_xml_1 o
on (t.app_name=o.app_name)
when matched then 
update set t.APP_TYPE=o.APP_TYPE,
t.APP_OWNER_NAME=(select L1_NAME||';'||L2_NAME||';'||L3_NAME from  app_contact_xml_1 
where   APP_NAME=o.app_name) 
when not matched then
if(o.app_name is not null) then
insert(t.APP_NAME,t.APP_TYPE,t.APP_OWNER_NAME) 
values(o.APP_NAME,o.APP_TYPE,o.L1_NAME||';'||o.L2_NAME||';'||o.L3_NAME)
end if;



I have included this whole in a procedure. But It is showing an error. So, is ther any way to put a condition in the 'when not matched part'.

Re: Merge with extra condition [message #346415 is a reply to message #346410] Mon, 08 September 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dint get your first point..

You use "o.app_name" in the select, remove the select and use "o.<other columns>" directly.

Quote:
for second point, I am sorry it was a typo. It is actually app_name.

Use "(select * from app_contact_xml_1 where app_name is not null) o" instead of "app_contact_xml_1 o" in "using" clause.

Regards
Michel

[Edit: add missing end of last sentence]

[Updated on: Mon, 08 September 2008 10:09]

Report message to a moderator

Re: Merge with extra condition [message #346418 is a reply to message #346390] Mon, 08 September 2008 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Since when does straight SQL support
>if(o.app_name is not null) then
construct?
SQL <> PL/SQL!!!!!!!
Re: Merge with extra condition [message #346420 is a reply to message #346390] Mon, 08 September 2008 09:03 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks Michel....
I do the same as you said..

Regards,
Pointers.
Previous Topic: Dynamic Memory allocation
Next Topic: ORA:01732
Goto Forum:
  


Current Time: Sat Dec 03 12:22:46 CST 2016

Total time taken to generate the page: 0.05578 seconds