Manipulating Data with DML (INSERT, UPDATE, DELETE, MERGE)
Manipulating Data with DML (INSERT, UPDATE, DELETE, MERGE)
This domain tests changing table data and managing the transaction. You must know single-row and multi-row INSERT forms, UPDATE and DELETE behavior, MERGE semantics, defaults, and commit or rollback effects.
Core rules
- INSERT VALUES adds one row; INSERT with a subquery adds many rows. Omitted columns receive their default or null. DEFAULT explicitly requests the column default.
- Multi-table INSERT uses ALL or FIRST with WHEN conditions to route source rows into one or more target tables.
- UPDATE changes every row satisfying WHERE; without WHERE it changes all rows. SET can assign multiple columns. A correlated subquery can supply values, but it must return one row per target row.
- DELETE removes rows satisfying WHERE; without WHERE it removes all rows. TRUNCATE is DDL, not DML, and cannot be rolled back.
- MERGE compares a source to a target and performs UPDATE, INSERT, or both. The ON condition must not match one target row to multiple source rows, or Oracle raises an error.
- DML changes are transactional until COMMIT or ROLLBACK. SAVEPOINT allows partial rollback. DML obtains row locks and can block conflicting changes.
Worked example
MERGE INTO target t USING source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.amount = s.amount WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
Each source row must match at most one target row. Matching rows are updated; unmatched source rows are inserted. The statement is one transaction and can be rolled back before commit.
Traps
Forgetting WHERE in UPDATE or DELETE affects the entire table. A subquery in SET that returns multiple rows fails. MERGE with duplicate source keys causes ORA-30926. Supplying null explicitly does not trigger a default unless DEFAULT ON NULL is defined. DDL such as TRUNCATE implicitly commits, so it cannot be undone with ROLLBACK.