Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle MERGE

Oracle MERGE

Summary: in this tutorial, you will learn how to use the Oracle MERGE statement to perform an update or insert data based on a specified condition.

Introduction to the Oracle MERGE statement

The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.

The following illustrates the syntax of the Oracle MERGE statement:

MERGE INTO target_table USING source_table ON search_condition WHEN MATCHED THEN UPDATE SET col1 = value1, col2 = value2,... WHERE <update_condition> [DELETE WHERE <delete_condition>] WHEN NOT MATCHED THEN INSERT (col1,col2,...) values(value1,value2,...) WHERE <insert_condition>;

Let’s examine the MERGE statement in detail:

First, specify the target table (target_table) which you want to update or insert into in the INTO clause.

Second, specify the source of data (source_table) to be updated or inserted in the USING clause.

Third, specify the search condition upon which the merge operation either updates or inserts in the ON clause.

For each row in the target table, Oracle evaluates the search condition:

  • If the result is true, then Oracle updates the row with the corresponding data from the source table.
  • In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.

The MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation.

Because the MERGE is a deterministic statement, you cannot update the same row of the target table multiple times in the same MERGE statement.

You can add an optional DELETE WHERE clause to the MATCHED clause to clean up after a merge operation. The DELETE clause deletes only the rows in the target table that match both ON and DELETE WHERE clauses.

Oracle MERGE prerequisites

To execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables. If you use the DELETE clause, you must also have the DELETE object privilege on the target table.

Oracle MERGE example

Suppose, we have two tables: members and member_staging.

We insert a new row to the members table whenever we have a new member. Then, the data from the members table is merged with the data of the member_staging table.

The following statements create the members and member_staging tables:

CREATE TABLE members ( member_id NUMBER PRIMARY KEY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, rank VARCHAR2(20) ); CREATE TABLE member_staging AS SELECT * FROM members;

The following INSERT statements insert sample data into the members and member_staging tables:

-- insert into members table INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze'); -- insert into member_staging table INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

When updating data from the members table to member_staging table, we should perform the following actions:

  • We update the rows with member id 1, 3, 4, and 6 because the rank or the last name of these members in these tables are different.
  • We insert the rows with member id 7 to 10 are because these rows exist in the members table but not in the member_staging table.

In total 8 rows should be merged.

Oracle MERGE example

The following is the MERGE statement that performs all of these actions in one shot.

MERGE INTO member_staging x USING (SELECT member_id, first_name, last_name, rank FROM members) y ON (x.member_id = y.member_id) WHEN MATCHED THEN UPDATE SET x.first_name = y.first_name, x.last_name = y.last_name, x.rank = y.rank WHERE x.first_name <> y.first_name OR x.last_name <> y.last_name OR x.rank <> y.rank WHEN NOT MATCHED THEN INSERT(x.member_id, x.first_name, x.last_name, x.rank) VALUES(y.member_id, y.first_name, y.last_name, y.rank);

The merge statement compares each row in the members table with each row in the member_staging table based on the values in the member_id columns (see the ON clause above).

If the values in member_id columns of both tables are equal, the MERGE statement updates the first name, last name, and rank from the members table to the member_stagingtable only if the values of first name, last name, or rank columns of both tables are different.

Otherwise, it inserts the row from the members table into the member_staging table.

Oracle returned 8 rows merged as expected.

In this tutorial, you have learned how to use the Oracle MERGE statement to update or insert data based on a specified condition.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle DELETE
Next The Ultimate Guide to Oracle INSERT ALL Statement

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.