Chapters 18 and 19 cover the more advanced DML patterns on the exam. MERGE is the centrepiece — it combines INSERT and UPDATE into a single statement and the exam tests every clause of its syntax. The other patterns covered here include DEFAULT values in DML, explicit DEFAULT keyword usage, and the behaviour of multi-row DML with constraints.
MERGE questions are not common in high numbers but when they appear, candidates who have not studied the syntax carefully tend to choose plausible-looking wrong answers.
- MERGE — the combined INSERT/UPDATE statement
- MERGE full syntax
- MERGE with WHEN NOT MATCHED
- MERGE with WHEN MATCHED — optional DELETE
- Making WHEN clauses optional
- DEFAULT values in INSERT and UPDATE
- Explicit DEFAULT keyword in DML
- DEFAULT with NULL — important distinction
- Multi-row DML and constraint checking
- Quick reference
- Practice questions
01 — MERGE — the combined INSERT/UPDATE statement
MERGE solves the "upsert" problem — when you have a source of data and you want to update matching rows in a target table and insert rows that don't yet exist, all in one operation. Without MERGE you would need separate UPDATE and INSERT statements with logic to determine which rows need which operation.
-- The problem MERGE solves: -- You have new employee data in a staging table (emp_updates) -- For each row: if the employee already exists, UPDATE them -- if they don't exist yet, INSERT them -- Without MERGE: two separate statements with subquery logic UPDATE employees SET salary = ... WHERE employee_id IN (SELECT employee_id FROM emp_updates); INSERT INTO employees ... SELECT * FROM emp_updates WHERE employee_id NOT IN (SELECT employee_id FROM employees); -- With MERGE: one statement handles both cases cleanly
02 — MERGE full syntax
The MERGE syntax has four key parts: the target table, the source, the join condition, and the WHEN clauses. The exam tests whether each part is in the right place.
MERGE INTO employees e -- target table USING emp_updates u -- source table (or subquery) ON (e.employee_id = u.employee_id) -- join condition WHEN MATCHED THEN -- row exists in target UPDATE SET e.salary = u.salary, e.department_id = u.department_id WHEN NOT MATCHED THEN -- row does not exist in target INSERT (employee_id, last_name, salary, department_id) VALUES (u.employee_id, u.last_name, u.salary, u.department_id);
| Part | Keyword | Purpose |
|---|---|---|
| Target | MERGE INTO | The table being updated or inserted into |
| Source | USING | The data source — a table, view, or subquery |
| Join condition | ON (...) | How to match source rows to target rows |
| Matched rows | WHEN MATCHED THEN UPDATE | What to do when source row matches target |
| Unmatched rows | WHEN NOT MATCHED THEN INSERT | What to do when source row has no match in target |
03 — MERGE with WHEN NOT MATCHED
WHEN NOT MATCHED fires for every source row that has no matching row in the target table. The INSERT syntax inside WHEN NOT MATCHED follows the same rules as a regular INSERT.
MERGE INTO employees e USING new_hires n ON (e.employee_id = n.employee_id) WHEN NOT MATCHED THEN INSERT (employee_id, last_name, email, hire_date, job_id, salary) VALUES (n.employee_id, n.last_name, n.email, n.hire_date, n.job_id, n.salary) WHERE n.hire_date >= TO_DATE('01-JAN-2024', 'DD-MON-YYYY'); -- The optional WHERE on WHEN NOT MATCHED filters which unmatched rows get inserted -- Source rows that don't match AND don't pass the WHERE are silently skipped
04 — MERGE with WHEN MATCHED — optional DELETE
WHEN MATCHED can include an optional DELETE clause in addition to UPDATE. A row that is matched and updated can also be deleted in the same MERGE operation if it satisfies the DELETE condition.
MERGE INTO employees e USING emp_changes c ON (e.employee_id = c.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = c.new_salary, e.job_id = c.new_job_id DELETE WHERE (c.status = 'TERMINATED'); -- Step 1: UPDATE is applied to all matched rows -- Step 2: Of the rows just updated, those where status = 'TERMINATED' are deleted -- The DELETE condition is evaluated AFTER the UPDATE, against the updated values
05 — Making WHEN clauses optional
Both WHEN MATCHED and WHEN NOT MATCHED are optional in a MERGE statement. You can include just one of them if you only need to handle one case.
-- UPDATE only — no INSERT for new rows MERGE INTO employees e USING salary_updates u ON (e.employee_id = u.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = u.new_salary; -- Unmatched source rows are silently ignored — no INSERT happens -- INSERT only — no UPDATE for existing rows MERGE INTO employees e USING new_hires n ON (e.employee_id = n.employee_id) WHEN NOT MATCHED THEN INSERT (employee_id, last_name, salary) VALUES (n.employee_id, n.last_name, n.salary); -- Matched rows are silently ignored — no UPDATE happens
| Clauses present | Behaviour |
|---|---|
| WHEN MATCHED only | Updates matched rows — unmatched source rows ignored |
| WHEN NOT MATCHED only | Inserts unmatched rows — matched rows ignored |
| Both clauses | Updates matched, inserts unmatched — full upsert |
| Neither clause | Syntax error — at least one WHEN clause required |
06 — DEFAULT values in INSERT and UPDATE
A DEFAULT value is defined at the column level in CREATE TABLE or ALTER TABLE. It is used automatically when a row is inserted without specifying that column. The exam tests the exact conditions under which Oracle uses the DEFAULT.
-- Column defined with DEFAULT CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE DEFAULT SYSDATE, status VARCHAR2(10) DEFAULT 'PENDING' ); -- DEFAULT is used when column is omitted from INSERT INSERT INTO orders (order_id) VALUES (1001); -- order_date gets SYSDATE, status gets 'PENDING' -- DEFAULT is NOT used when NULL is explicitly inserted INSERT INTO orders (order_id, status) VALUES (1002, NULL); -- status gets NULL — not 'PENDING' -- Explicit NULL overrides the DEFAULT value
VALUES (..., NULL), Oracle stores NULL — it does not fall back to the DEFAULT. The DEFAULT only applies when the column is omitted entirely from the INSERT column list. The exam tests this distinction with questions about what value ends up in the column.
07 — Explicit DEFAULT keyword in DML
Oracle allows you to write the keyword DEFAULT explicitly in INSERT and UPDATE statements to force the default value to be used, even when you are providing values for other columns in the same statement.
-- Using DEFAULT explicitly in INSERT VALUES INSERT INTO orders (order_id, order_date, status) VALUES (1003, DEFAULT, 'EXPRESS'); -- order_date gets SYSDATE (the DEFAULT for that column) -- status gets 'EXPRESS' (explicit value overrides the DEFAULT) -- Using DEFAULT in UPDATE SET UPDATE orders SET status = DEFAULT WHERE order_id = 1003; -- Resets status to 'PENDING' (its DEFAULT value) -- Useful when you want to reset a column to its defined default -- If no DEFAULT is defined, DEFAULT keyword inserts NULL INSERT INTO employees (employee_id, last_name, commission_pct) VALUES (400, 'Patel', DEFAULT); -- commission_pct has no DEFAULT defined — stores NULL
08 — DEFAULT with NULL — important distinction
The exam tests the three-way distinction between: a column having no DEFAULT, a column whose DEFAULT is NULL, and explicitly inserting NULL. These produce the same stored result but have different implications for constraint checking.
| Scenario | Column has DEFAULT 'PENDING' | Column has no DEFAULT |
|---|---|---|
| Column omitted from INSERT | Stores 'PENDING' | Stores NULL |
| INSERT ... VALUES (..., NULL) | Stores NULL — default ignored | Stores NULL |
| INSERT ... VALUES (..., DEFAULT) | Stores 'PENDING' | Stores NULL (no default to apply) |
| UPDATE SET col = DEFAULT | Sets to 'PENDING' | Sets to NULL |
09 — Multi-row DML and constraint checking
When a DML statement affects multiple rows, Oracle checks constraints after all rows have been processed — not row by row. This is called deferred within a statement and it matters for self-referencing tables.
-- Classic example: swapping manager IDs in a self-referencing table -- If Oracle checked row by row, intermediate states would violate FK UPDATE employees SET manager_id = CASE WHEN manager_id = 100 THEN 101 WHEN manager_id = 101 THEN 100 END WHERE manager_id IN (100, 101); -- This works because FK constraints are checked after all rows are updated -- not after each individual row -- Constraint violations that affect ALL rows in a statement fail the whole statement INSERT INTO employees SELECT * FROM emp_staging; -- If even one row violates a constraint, the entire INSERT fails -- Oracle does not do partial inserts — it's all or nothing per statement
10 — Quick reference
| Rule | Detail |
|---|---|
| MERGE INTO ... USING ... ON | Target, source, join condition — all required |
| WHEN MATCHED — cannot update join column | Raises error if ON column appears in UPDATE SET |
| WHEN MATCHED DELETE WHERE | Deletes rows after UPDATE — condition uses updated values |
| Both WHEN clauses are optional | Must have at least one — neither raises error |
| MERGE is DML | Participates in transactions — can ROLLBACK |
| Omitting column uses DEFAULT | Column not in INSERT list gets its DEFAULT value |
| Explicit NULL overrides DEFAULT | VALUES (..., NULL) stores NULL — ignores DEFAULT |
| DEFAULT keyword in DML | Forces the column's defined default — stores NULL if no default exists |
| Multi-row DML is all or nothing | One constraint violation fails the entire statement |
| Constraint check timing | Checked after all rows processed — not row by row |
11 — Practice questions
e.employee_id = u.employee_id in the UPDATE SET. What happens?INSERT INTO orders (order_id, status) VALUES (100, NULL);What value is stored in STATUS?
Chapters 18 and 19 cover the full MERGE syntax, DEFAULT behaviour, and advanced DML constraint rules — with practice sets for every pattern. Instant PDF download.
No comments:
Post a Comment