How to Perform UPSERT Operations in Oracle Databases?

Perform UPSERT operation in Oracle database

UPSERT operation - a combination of update and insert - modifies the data in the table. Oracle lacks a dedicated UPSERT statement, so the question arises of how to accomplish this efficiently.

Solution: MERGE statement

Oracle provides the MERGE statement, which merges data from one table to another. It allows three operations: insert, update and delete. By using a DUAL table (containing a single row and a single column), we can simulate a UPSERT operation.

Example:

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

Usage:

<code class="language-sql">-- 创建必要的表
drop table mergetest;
create table mergetest(a number, b number);

-- 调用过程以执行UPSERT
call ups(10);
call ups(10);
call ups(20);

-- 验证结果
select * from mergetest;</code>

Output:

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

This MERGE statement ensures that if a row matching the specified key (xa) exists, the row is updated; otherwise, a new row is inserted.

The above is the detailed content of How to Perform UPSERT Operations in Oracle Databases?. For more information, please follow other related articles on the PHP Chinese website!