Data Manipulation Language: INSERT, UPDATE, DELETE, and MERGE
Data Manipulation Language
This domain tests INSERT, UPDATE, DELETE, and MERGE, including the effect of constraints and transactions. DML changes data and remains uncommitted until COMMIT or an implicit commit.
Core rules
- INSERT INTO table (columns) VALUES (values) adds one row. INSERT INTO table (columns) SELECT ... adds many rows.
- UPDATE table SET column = value WHERE condition changes matching rows. Without WHERE, every row is updated.
- DELETE FROM table WHERE condition removes rows. Without WHERE, all rows are deleted. TRUNCATE is DDL, not DML.
- MERGE combines insert and update: MERGE INTO target t USING source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val).
- Constraints are checked at statement level unless deferred. A failed statement rolls back its own changes.
- DML is transactional; DDL and many DCL operations commit.
Worked example
MERGE INTO products p USING new_products n ON (p.product_id = n.product_id) WHEN MATCHED THEN UPDATE SET p.price = n.price WHEN NOT MATCHED THEN INSERT (product_id, price) VALUES (n.product_id, n.price); Existing IDs get the new price; new IDs are inserted. Ensure the source has no duplicate join keys, or Oracle raises ORA-30926.
Traps
- Omitting WHERE in UPDATE or DELETE.
- Trying to update a column referenced in the MERGE ON clause.
- Assuming DELETE can be rolled back after TRUNCATE; it cannot.
- Forgetting that a constraint violation aborts the statement.