How Can I Perform an UPSERT Operation in Oracle Database?

UPSERT operation in Oracle database

UPSERT (Update or Insert into Table) operation provides a convenient way to modify existing rows or add new rows to a table based on whether a row with matching data exists.

Challenges of UPSERT in Oracle Database

Unlike some other databases, Oracle does not provide a dedicated UPSERT statement. To overcome this problem, we use the MERGE statement, which is a powerful mechanism for combining data from multiple data sources.

Perform Oracle UPSERT operation using MERGE

The MERGE statement operates two tables, one as the target table (mergetest here) and the other as a placeholder (DUAL). Using this technology, we can implement the UPSERT function:

1. MERGE into mergetest m using dual on (a = xa): This row identifies the target table and establishes the join condition based on column "a".
2. when not matched then insert (a,b) values ​​(xa,1): This insert clause will create a new row if no matching row is found in "mergetest", Where column "a" is set to "xa" and column "b" is set to 1.
3. when matched then update set b = b 1: This update clause increments the value of column "b" by 1 if a matching row is found.

Example implementation

Consider the following code:

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

This procedure defines a function to perform the UPSERT operation.

Usage:

<code class="language-sql">call ups(10);
call ups(10);
call ups(20);
select * from mergetest;</code>

Output:

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

Conclusion

By using the MERGE statement, we can effectively implement the UPSERT function in Oracle, allowing us to modify or insert data in the table based on whether matching rows exist.

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