However from a C perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was. None of the answers given so far is safe in the face of concurrent accesses , as pointed out in Tim Sylvester's comment, and will raise exceptions in case of races. As an example, here's how Grommit's code can be wrapped in a loop to make it safe when run concurrently:.
I'd like Grommit answer, except it require dupe values. In the first example the update does an index lookup. It has to, in order to update the right row.
Oracle opens an implicit cursor, and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist. But the insert is an independent command and it has to do a second lookup. I don't know the inner workings of the merge command but since the command is a single unit, Oracle could execute the correct insert or update with a single index lookup.
I think merge is better when you do have some processing to be done that means taking data from some tables and updating a table, possibly inserting or deleting rows. But for the single row case, you may consider the first case since the syntax is more common. The question of whether to insert or update first is also application dependent. Are you expecting more inserts or more updates?
The one that is most likely to succeed should go first. If you pick the wrong one you will get a bunch of unnecessary index reads. Not a huge deal but still something to consider. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question.
Asked 13 years, 2 months ago. Active 6 months ago. Viewed k times. The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data: if table t has a row exists that has key X: update t set mystuff Mark Harrison Mark Harrison k gold badges silver badges bronze badges. Add a comment. Active Oldest Votes. Apparently the "merge into" statement is not atomic.
It can result in "ORA unique constraint" when used concurrently. The check for existence of a match and the insertion of a new record are not protected by a lock, so there is a race condition.
To do this reliably, you need to catch this exception and either re-run the merge or do a simple update instead. With this type of insert, you may wish to check for the number of rows being inserted.
You can determine the number of rows that will be inserted by running the following Oracle SELECT statement before performing the insert. Question: I am setting up a database with clients. The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
In this case, the question to ask whether the procedure will mostly be used to update existing employee records, or insert new ones.
Maybe there will be no clear winner, but there often is. If the procedure is expected to mostly insert new records and rarely update existing ones, then use the following pattern assuming there are unique constraints on the database to prevent duplicate employees being created :.
This code relies of the database to tell you the record already exists based on the integrity constraints on the table, which is much more efficient and less error prone that attempting to do it yourself. In this case, if it contain zero, it means the update failed to find any rows to update and therefore the record needs to be inserted instead.
You can get away with even less code that this. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.
0コメント