Skip to Main Content

SQL & PL/SQL

Announcement

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 stmt do both INSERT & UPDATE?

SebaVastaMay 31 2024

Does the MERGE statement do BOTH update and insert or only either of the operation only??

This post has been answered by Mike Kutz on Jun 1 2024
Jump to Answer

Comments

Paulzip May 31 2024

For a given row, it only does one, because logically only one can be true:

when MATCHED then

when not MATCHED then

Stew Ashton May 31 2024 — edited on May 31 2024

[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

SebaVasta May 31 2024

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?

Jonathan Lewis May 31 2024

A couple of other details relating to “insert and update”:

  • If you have two rows in the “new” data that match an existing row in the “old” data, then Oracle will probably raise error “ORA-30926: unable to get a stable set of rows in the source tables”.
  • If you have two rows in the “new” data that don't have a match in the “old” data but which would match the same row if it existed, Oracle will insert both rows, it won't insert the first and then update it with the second. (If you have a uniqueness constraint that would be violated by the second insert then you will see the “duplicate key / unique constraint” error (ORA-00001) of course.

Regards

Jonathan Lewis

Jonathan Lewis May 31 2024

So this merge will update 1 row and and insert 1. Is this correct?

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.

Regards

Jonathan Lewis

P.S. – Do you have any reason to think that the order might matter?

SebaVasta May 31 2024 — edited on May 31 2024

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.

Jonathan Lewis May 31 2024

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.)

Regards

Jonathan Lewis

Mike Kutz Jun 1 2024
Answer

sounds like a bad design.

Databases don't really do “order”. This is why merge is so fast.

If you need an order, you must provide an order by clause. – (iirc merge ignores any order by )

Marked as Answer by SebaVasta · Nov 6 2024
SebaVasta Sep 2 2024

This ones on hold as it looks like bad design. We might have to rewrite the MERGE in PL/SQL.😒

SebaVasta Nov 6 2024 — edited on Nov 6 2024

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.

1 - 10

Post Details

Added on May 31 2024
10 comments
372 views