Manipulating Data
What the domain covers
DML changes table data: INSERT, UPDATE, DELETE, and MERGE. This domain tests transaction control, row locks, and safe predicates.
Core rules
- INSERT INTO table (columns) VALUES (...) adds one row; INSERT INTO ... SELECT adds rows from a query.
- UPDATE table SET column = expression WHERE condition changes existing rows. The WHERE clause determines which rows; without it, every row changes.
- DELETE FROM table WHERE condition removes rows. DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot.
- A transaction starts with the first DML and ends with COMMIT or ROLLBACK. SAVEPOINT creates a marker for partial rollback.
- DML locks rows until the transaction ends. Other sessions wait if they try to change the same rows.
- MERGE performs conditional insert or update in one statement based on a source query.
Worked example
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 50;
Reasoning: the SET expression reads the current salary of each target row and multiplies it by 1.10. Only rows whose department_id is 50 are changed. The statement is not committed automatically; another session does not see the new salaries until COMMIT. If the WHERE clause were omitted, every employee salary would increase.
Traps
An UPDATE or DELETE without WHERE affects all rows. Do not confuse DELETE with DROP: DELETE removes rows, DROP removes the table. COMMIT ends the transaction and cannot be undone. A failed statement does not automatically roll back the whole transaction. DELETE can be rolled back; TRUNCATE cannot.