10.2 MERGE and Multitable Inserts

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

10.2 MERGE and Multitable Inserts

Key Takeaways Explain This Topic AI

The MERGE statement performs conditional upserts (inserting, updating, and optionally deleting target rows) based on match conditions evaluated against a source dataset in a single atomic SQL operation.

Under strict Oracle SQL rules (ORA-38104), columns referenced in the MERGE statement's ON join condition cannot be modified in the WHEN MATCHED THEN UPDATE clause.

The optional DELETE WHERE clause inside WHEN MATCHED removes only rows that were updated by the MERGE operation and satisfy the deletion predicate.

Unconditional INSERT ALL inserts every source row into all listed target tables, while conditional INSERT ALL evaluates every WHEN condition for each row without short-circuiting.

Conditional INSERT FIRST evaluates WHEN conditions sequentially and inserts the row only into the first matching table, short-circuiting and skipping all subsequent WHEN clauses and the ELSE clause.

Last updated: August 2026

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:

The MERGE Statement : Combines conditional inserts, updates, and deletes into a single atomic statement (often called an "upsert").

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 × 3 = 300 100 \times 3 = 300 100 × 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)

Restriction Description & Exam Impact

Target Object Types Multitable INSERT can only target tables , not views or materialized views.

Remote Databases Target tables cannot be remote objects accessed via Database Links ( @dblink ).

Sequences If a sequence ( seq.NEXTVAL ) is referenced in multiple INTO clauses, Oracle generates only one sequence number per source row and distributes that identical number across all INTO clauses for that row.

TABLE Collection Expressions Cannot use TABLE() collection expressions in multitable inserts.

Column Limit The total number of target columns across all INTO clauses cannot exceed 999.