How Can I Implement UPSERT Functionality in Oracle?

Oracle UPSERT: Techniques and Implementation

Oracle doesn't natively support UPSERT operations (simultaneous updates or inserts based on matching criteria). However, several methods effectively replicate this functionality. A common and efficient approach uses the MERGE statement.

The MERGE statement facilitates data manipulation between two tables. Using DUAL as a source table provides a concise UPSERT implementation:

<code class="language-sql">create or replace procedure ups(xa number) as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;</code>

Let's illustrate with a table and procedure calls:

<code class="language-sql">drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;</code>

This yields:

<code>A                      B
---------------------- ----------------------
10                     2
20                     1</code>

As shown, the MERGE-based UPSERT updates existing records (matching the key) and inserts new ones (non-matching keys). This method provides a clean and efficient way to uphold data integrity.

The above is the detailed content of How Can I Implement UPSERT Functionality in Oracle?. For more information, please follow other related articles on the PHP Chinese website!