10.2 MERGE and Multitable Inserts

10.2 MERGE and Multitable Inserts

Enterprise data processing frequently requires synchronizing operational tables with external feeds, data warehouses, or staging buffers. Traditionally, synchronizing a target table required executing multiple distinct SQL statements: a SELECT to identify existing keys, an UPDATE for existing records, and an INSERT for new records.

To optimize performance and simplify code, Oracle SQL provides advanced DML constructs:

1. The MERGE Statement: Combines conditional inserts, updates, and deletes into a single atomic statement (often called an "upsert").
2. Multitable INSERT Statements: Allows a single source query to populate multiple destination tables simultaneously using INSERT ALL or INSERT FIRST.

Understanding the exact syntax, short-circuit execution rules, column update restrictions, and pivoting techniques for these statements is heavily emphasized on the Oracle Database SQL Certified Associate (1Z0-071) exam.

---

The MERGE Statement Architecture

The MERGE statement selects records from one or more source tables or subqueries and conditionally updates, deletes, or inserts data into a target table or updatable view based on a join condition.

+-----------------------------------------------------------------------------------+
|                                MERGE STATEMENT SYNTAX                             |
|                                                                                   |
|  MERGE INTO target_table [alias_t]                                                |
|  USING source_table_or_subquery [alias_s]                                         |
|  ON (join_condition)                                                             |
|  [WHEN MATCHED THEN                                                               |
|     UPDATE SET alias_t.col1 = expr1 [, alias_t.col2 = expr2, ...]                 |
|     [WHERE update_filter_condition]                                               |
|     [DELETE WHERE delete_filter_condition]                                        |
|  ]                                                                                |
|  [WHEN NOT MATCHED THEN                                                           |
|     INSERT [(col1, col2, ...)]                                                    |
|     VALUES (expr1, expr2, ...)                                                    |
|     [WHERE insert_filter_condition]                                               |
|  ];                                                                               |
+-----------------------------------------------------------------------------------+

                            MERGE EXECUTION FLOW
                                      |
                      +---------------+---------------+
                      | For each row in SOURCE dataset|
                      +---------------+---------------+
                                      |
                                      v
                     /---------------------------------\
                    /    Does source row match target   \
                   <     based on ON (join_condition)?   >
                    \                                   /
                     \---------------------------------/
                               /             \
                     YES (MATCHED)         NO (NOT MATCHED)
                             /                 \
                            v                   v
                +-----------------------+   +-----------------------+
                | Execute WHEN MATCHED  |   | Execute WHEN NOT      |
                | UPDATE SET ...        |   | MATCHED INSERT ...    |
                +-----------+-----------+   +-----------------------+
                            |
                            v
                /-----------------------\
               /   Is DELETE WHERE       \
              <    predicate specified    >
               \   and TRUE for row?     /
                \-----------------------/
                     /             \
                   YES             NO
                   /                 \
                  v                   v
       +--------------------+   +---------------------------+
       | Delete updated row |   | Retain updated target row |
       | from target table  |   +---------------------------+
       +--------------------+

Concrete Example: Inventory Synchronization

Suppose an e-commerce platform receives a daily batch of product inventory updates in table STAGE_PRODUCTS that must be merged into the production table PROD_INVENTORY:

MERGE INTO prod_inventory tgt
USING stage_products src
ON (tgt.product_id = src.product_id)
WHEN MATCHED THEN
    UPDATE SET 
        tgt.unit_price   = src.unit_price,
        tgt.stock_qty    = tgt.stock_qty + src.delivered_qty,
        tgt.last_updated = SYSDATE
    WHERE src.delivered_qty > 0
    DELETE WHERE tgt.stock_qty <= 0
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, unit_price, stock_qty, last_updated)
    VALUES (src.product_id, src.product_name, src.unit_price, src.delivered_qty, SYSDATE)
    WHERE src.delivered_qty > 0;

---

Critical MERGE Rules and Restrictions (Tested on 1Z0-071)

Rule 1: Cannot Update Join Columns in the ON Clause (ORA-38104)

This is one of the most frequently tested rules on the certification exam:

The Join Column Immutability Rule: You CANNOT update any column in the WHEN MATCHED THEN UPDATE clause that is referenced in the ON (join_condition) clause! Attempting to do so raises ORA-38104.

-- INVALID MERGE: Raises ORA-38104
MERGE INTO employees tgt
USING new_roster src
ON (tgt.employee_id = src.emp_id)
WHEN MATCHED THEN
    UPDATE SET 
        tgt.employee_id = src.emp_id, -- ERROR: TARGET.EMPLOYEE_ID is in the ON clause!
        tgt.salary      = src.salary;

Oracle Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "TGT"."EMPLOYEE_ID".

Rule 2: Clauses are Optional (Oracle 10g and later)

• You do not have to include both WHEN MATCHED and WHEN NOT MATCHED clauses.
• A MERGE statement containing only WHEN MATCHED (pure update/delete) or only WHEN NOT MATCHED (pure insert) is 100% valid.

Rule 3: The DELETE WHERE Sub-Clause Behavior

• The DELETE WHERE clause can appear only inside the WHEN MATCHED THEN UPDATE clause. It cannot stand alone without an UPDATE.
• The DELETE WHERE clause evaluates the row after the UPDATE has been applied.
• It deletes only rows in the target table that were matched and updated by that specific MERGE statement and satisfy the DELETE WHERE condition. It will not delete untouched target rows that happen to match the DELETE WHERE predicate!

---

Multitable INSERT Statements

Oracle SQL allows inserting rows into one or more target tables using a single INSERT statement driven by a single source SELECT query. There are two primary categories:

+-----------------------------------------------------------------------------------+
|                         MULTITABLE INSERT CLASSIFICATION                          |
|                                                                                   |
|  1. UNCONDITIONAL INSERT ALL:                                                     |
|     - Inserts every row from the source query into ALL listed target tables.      |
|     - Commonly used for data replication and table pivoting.                      |
|                                                                                   |
|  2. CONDITIONAL INSERT ALL:                                                       |
|     - Evaluates multiple WHEN conditions independently for each source row.       |
|     - A single source row can be inserted into MULTIPLE tables if multiple WHEN   |
|       conditions evaluate to TRUE.                                                |
|                                                                                   |
|  3. CONDITIONAL INSERT FIRST:                                                     |
|     - Evaluates WHEN conditions sequentially in order.                            |
|     - Short-circuits: Inserts into the FIRST matching table and SKIPS all others!  |
+-----------------------------------------------------------------------------------+

---

1. Unconditional INSERT ALL

In an unconditional INSERT ALL, every row returned by the subquery is inserted into every target table specified in the INTO clauses.

-- Unconditional Multitable Insert: Populates 3 audit/backup tables simultaneously
INSERT ALL
    INTO orders_archive VALUES (order_id, customer_id, order_total, order_date)
    INTO daily_sales_log VALUES (order_id, order_total, SYSDATE)
    INTO customer_activity (cust_id, last_action_date) VALUES (customer_id, order_date)
SELECT order_id, customer_id, order_total, order_date
FROM current_orders
WHERE order_date = TRUNC(SYSDATE);

If the source query returns 100 rows, Oracle executes $100 \times 3 = 300$ total row insertions across the three destination tables.

---

2. Conditional INSERT ALL vs. Conditional INSERT FIRST

The difference in evaluation semantics between INSERT ALL and INSERT FIRST is a cornerstone of the 1Z0-071 exam.

+-----------------------------------------------------------------------------------+
|                 CONDITIONAL INSERT ALL vs INSERT FIRST EXECUTION                  |
|                                                                                   |
|  Source Row: Salary = $15,000                                                     |
|                                                                                   |
|  Statement A (INSERT ALL):                                                        |
|    WHEN salary >= 10000 THEN INTO high_sal_tab   --> TRUE  (Inserted!)            |
|    WHEN salary >= 5000  THEN INTO mid_sal_tab    --> TRUE  (Inserted!)            |
|    ELSE                      INTO low_sal_tab    --> Evaluated ONLY if 0 matches  |
|    Result: Row inserted into BOTH high_sal_tab AND mid_sal_tab!                   |
|                                                                                   |
|  Statement B (INSERT FIRST):                                                      |
|    WHEN salary >= 10000 THEN INTO high_sal_tab   --> TRUE  (Inserted! STOP!)      |
|    WHEN salary >= 5000  THEN INTO mid_sal_tab    --> SKIPPED (Short-circuited)    |
|    ELSE                      INTO low_sal_tab    --> SKIPPED                      |
|    Result: Row inserted ONLY into high_sal_tab!                                   |
+-----------------------------------------------------------------------------------+

Concrete Comparison Code Example

-- CONDITIONAL INSERT ALL: All matching branches fire
INSERT ALL
    WHEN salary >= 10000 THEN
        INTO exec_salaries VALUES (employee_id, salary)
    WHEN commission_pct IS NOT NULL THEN
        INTO commission_staff VALUES (employee_id, salary, commission_pct)
    ELSE
        INTO standard_payroll VALUES (employee_id, salary)
SELECT employee_id, salary, commission_pct FROM employees;

• If an employee has salary = 12000 and commission_pct = 0.20, both WHEN conditions evaluate to TRUE. The employee is inserted into both EXEC_SALARIES and COMMISSION_STAFF.
• The ELSE clause executes only for employees where neither condition is met.

-- CONDITIONAL INSERT FIRST: Short-circuits on first TRUE condition
INSERT FIRST
    WHEN salary >= 10000 THEN
        INTO tier1_salaries VALUES (employee_id, salary)
    WHEN salary >= 5000 THEN
        INTO tier2_salaries VALUES (employee_id, salary)
    ELSE
        INTO tier3_salaries VALUES (employee_id, salary)
SELECT employee_id, salary FROM employees;

• If an employee has salary = 12000, the first WHEN condition (salary >= 10000) is TRUE. The row is inserted into TIER1_SALARIES.
• Oracle immediately terminates evaluation for that row. The second WHEN condition is never evaluated for that employee, preventing duplicate insertions across salary tiers.

---

3. Pivoting INSERT Statements

A classic application of unconditional INSERT ALL is pivoting (converting wide, non-normalized spreadsheets or flat records with multiple repeating quarterly columns into multiple normalized rows).

+-----------------------------------------------------------------------------------+
|                                 PIVOTING WITH DML                                 |
|                                                                                   |
|  WIDE / DENORMALIZED TABLE (SALES_BY_QUARTER):                                    |
|  +---------+----------+----------+----------+----------+                          |
|  | EMP_ID  | Q1_SALES | Q2_SALES | Q3_SALES | Q4_SALES |                          |
|  | 101     | 5000     | 7500     | 6200     | 9000     |                          |
|  +---------+----------+----------+----------+----------+                          |
|                                                                                   |
|                                       | (Unconditional INSERT ALL)                |
|                                       v                                           |
|  NORMALIZED TABLE (NORMALIZED_SALES):                                             |
|  +---------+---------+--------+                                                   |
|  | EMP_ID  | QUARTER | AMOUNT |                                                   |
|  +---------+---------+--------+                                                   |
|  | 101     | Q1      | 5000   |                                                   |
|  | 101     | Q2      | 7500   |                                                   |
|  | 101     | Q3      | 6200   |                                                   |
|  | 101     | Q4      | 9000   |                                                   |
|  +---------+---------+--------+                                                   |
+-----------------------------------------------------------------------------------+

-- Pivoting 1 wide row into 4 normalized rows using INSERT ALL
INSERT ALL
    INTO normalized_sales (emp_id, quarter_code, sales_amount) VALUES (emp_id, 'Q1', q1_sales)
    INTO normalized_sales (emp_id, quarter_code, sales_amount) VALUES (emp_id, 'Q2', q2_sales)
    INTO normalized_sales (emp_id, quarter_code, sales_amount) VALUES (emp_id, 'Q3', q3_sales)
    INTO normalized_sales (emp_id, quarter_code, sales_amount) VALUES (emp_id, 'Q4', q4_sales)
SELECT emp_id, q1_sales, q2_sales, q3_sales, q4_sales
FROM sales_by_quarter;

---

Multitable INSERT Restrictions (Exam Checklist)