Oracle | Merge Statements

Oracle Merge Statements Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Usage Note Primarily of value when moving large amounts of data in data warehouse situations according to some people but in my opinion these statements don't belong anywhere unless the data volumes are so small as to be almost laughable. The reason I will not use these statements is very simply that they are brain dead. By which I mean that they assume there is an equal probability of the record being inserted or updated or deleted: This is never the case.

If the probability is that the record exists to be updated then use a FORALL UPDATE with SAVE EXCEPTIONS or an error logging table (DBMS_ERRLOG) and clean up the small number of failures that occur.

Demo tables and data conn uwclass/uwclass@pdbdev CREATE TABLE employee ( employee_id NUMBER(5), first_name VARCHAR2(20), last_name VARCHAR2(20), dept_no NUMBER(2), salary NUMBER(10)); INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000); INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000); INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000); INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000); INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000); INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000); INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000); CREATE TABLE bonuses ( employee_id NUMBER, bonus NUMBER DEFAULT 100); INSERT INTO bonuses (employee_id) VALUES (1); INSERT INTO bonuses (employee_id) VALUES (2); INSERT INTO bonuses (employee_id) VALUES (4); INSERT INTO bonuses (employee_id) VALUES (6); INSERT INTO bonuses (employee_id) VALUES (7); COMMIT; SELECT * FROM employee; SELECT * FROM bonuses;

Merge Statement

Conditional INSERT, UPDATE, or DELETE MERGE <hint> INTO <table_name> USING <table_view_or_query> ON (<condition>) WHEN MATCHED THEN <update_clause> DELETE <where_clause> WHEN NOT MATCHED THEN <insert_clause> [LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

MERGE INTO bonuses b

      USING (

        SELECT employee_id, salary, dept_no

        FROM employee

        WHERE dept_no =20) e

      ON  (b.employee_id = e.employee_id)

      WHEN MATCHED THEN

        UPDATE SET b.bonus = e.salary * 0.1

        DELETE WHERE (e.salary < 40000)

      WHEN NOT MATCHED THEN

        INSERT (b.employee_id, b.bonus)

        VALUES (e.employee_id, e.salary * 0.05)

        WHERE (e.salary > 40000);

      

      SELECT * FROM bonuses;