For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Does the MERGE statement do BOTH update and insert or only either of the operation only??
For a given row, it only does one, because logically only one can be true:
when MATCHED then
when not MATCHED then
[I submitted this before I saw @paulzip s identical reply…]
For each row, the row either matches or it doesn't. If it matches there is an update, if it doesn't there is an insert.
If one row matches and another doesn't, both operations happen, but on different rows.
Best regards, Stew Ashton
Thanks for the prompt replies. Can you tell me this also:
The MERGE INTO SQL brings 11 rows.
The USING SQL brings 2 rows. In this 1 row matches with with one of the 11 rows.
So this merge will update 1 row and and insert 1. Is this correct?
If so which order will they do it? Will they update it first and do the insert or insert first and do the update next?
A couple of other details relating to “insert and update”:
Regards
Jonathan Lewis
Yes (unless the insert causes a “duplicate key” error, of course), in which case the whole merge will roll back.
If so which order will they do it?
I'm not sure that there is a generic answer to this question. Oracle will do a join between the two tables - an outer join if if you both the “matched” and ""not matched" clauses. The execution plan that the optimizer picks for that join and the volume of data inserted/updated could have a significant effect on the actual mechanics. Bear in mind that the merge command also allows for a “delete when” clause which may also have to be factored in to the mechanics.
I may have some notes, possibly unpublished, on this - I'll check to see if I can find them.
P.S. – Do you have any reason to think that the order might matter?
Yes, our column values are populated in such way and there are constraints on the table that first it has to UPDATE first then only INSERT.
You are going to have one row updated and one row inserted. In principle you can have no idea where that inserted row might physically be in the table, so how can your column values require that the update happens before the insert. (Remember that if the insert takes place the update will not be updating the row just inserted.)
sounds like a bad design.
Databases don't really do “order”. This is why merge is so fast.
merge
If you need an order, you must provide an order by clause. – (iirc merge ignores any order by )
order by
This ones on hold as it looks like bad design. We might have to rewrite the MERGE in PL/SQL.😒
Yes, looks like 11g R2 MERGE ignore any ORDER BY. Confirmed by ChatGPT also. I had to do a workaround (store the problem field values and make problem field values to NULL before the merge, and update the rows after the merge) and now it works.