Home » SQL & PL/SQL » SQL & PL/SQL » To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged)
To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384220] Mon, 02 February 2009 22:47 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
Hi,

I am using oracle 9.2.0.8.0 release.

 
 Begin
 Merge Into Emp2 e2
      Using emp e
       On (E.Emp_Id = E2.Emp_ID)
     When Matched Then
       Update Set
             E2.Modified_DATE =Sysdate
      When Not Matched Then --avoid Insertions
       Insert (Emp_ID)
         Values(1/0);--To avoid insertions i used this
 Exception
 When Others Then
  Dbms_Output.Put_Line(Sqlerrm);


I am getting zero divisor error.

Here when conditions matches i want to update modified_date.

When not matched i do not want to insert the values.

I cannot use normal update statement since i have almost all 30000 records in emp2 table.

Kindly suggest me how can i avoid insert operations in merge.
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384222 is a reply to message #384220] Mon, 02 February 2009 23:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a condition that prevent from falling in "no match" case:
using (select null from emp where emp_id in (select emp_id from emp2)

Regards
Michel
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384224 is a reply to message #384222] Mon, 02 February 2009 23:08 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
(select null from emp where emp_id in (select emp_id from emp2)

Will this statement doesn't degrade the performance because it contains subquery and in clause.

Kindly suggest if i am wrong

Thanks for your reply
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384243 is a reply to message #384224] Tue, 03 February 2009 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but it does what you want and so it is better than a statement that is faster but does not fit the requirements.

Regards
Michel
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384251 is a reply to message #384243] Tue, 03 February 2009 00:37 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks Michel,

Does anyone extend their help on this topic by keeping performance in mind.

Thanks

Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384293 is a reply to message #384251] Tue, 03 February 2009 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've only got 30,000 rows in Emp2, YOu might as well use an UPDATE:
UPDATE EMP2 e2
SET    e2.MODIFIED_DATE =sysdate
WHERE  e2.EMP_ID IN (SELECT e.EMP_ID
                     FROM   EMP e);


Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384295 is a reply to message #384293] Tue, 03 February 2009 03:09 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
But this logic goes to production environment. again it may come back if i use like this
UPDATE EMP2 e2
SET    e2.MODIFIED_DATE =sysdate
WHERE  e2.EMP_ID IN (SELECT e.EMP_ID
                     FROM   EMP e);
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384303 is a reply to message #384295] Tue, 03 February 2009 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I don't understand what you're saying.

The UPDATE statement will produce the same end result as the merge, and if you've got the right indexes in plkace, it's performance on a 30,000 row table should be comparable.

Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384304 is a reply to message #384303] Tue, 03 February 2009 03:28 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks for that.

I have used bulk binding concept. now its working fine.

Thanks for all
Re: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged) [message #384307 is a reply to message #384304] Tue, 03 February 2009 03:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Bulk binding?

No-one's suggested a bulk binding based soluution.

Could you share your chosen solution with us?
Previous Topic: how to display null rows using oracle query
Next Topic: getting compilation errors with procedure
Goto Forum:
  


Current Time: Fri Dec 02 16:39:36 CST 2016

Total time taken to generate the page: 0.24501 seconds