10.1 INSERT, UPDATE, and DELETE Statements

10.1 INSERT, UPDATE, and DELETE Statements

In relational database systems, Data Manipulation Language (DML) comprises the core SQL operations that allow users and applications to create, modify, and delete data stored within database tables and views. Unlike Data Definition Language (DDL) commands—which alter schema object structures and metadata in the data dictionary—DML commands operate directly on table data blocks. Furthermore, all DML modifications occur within the context of a database transaction, remaining private to the issuing session until explicitly or implicitly committed.

Mastering the syntax nuances, column omission behaviors, subquery integration patterns, and storage implications of INSERT, UPDATE, and DELETE—along with the critical architectural distinctions between DELETE and TRUNCATE—is an essential requirement for the Oracle Database SQL Certified Associate (1Z0-071) examination.

---

The INSERT Statement

The INSERT statement creates new rows in an existing table or updatable view. Oracle SQL supports two primary mechanisms for inserting data: inserting single rows using the VALUES clause, and inserting multiple rows using a SELECT subquery.

+-----------------------------------------------------------------------------------+
|                             INSERT SYNTAX VARIATIONS                              |
|                                                                                   |
|  1. VALUES Clause (Single-Row Insert):                                            |
|     INSERT INTO table_name [(column1, column2, ...)]                              |
|     VALUES (value1, value2, ...);                                                 |
|                                                                                   |
|  2. Subquery Insert (Multi-Row Batch Insert):                                     |
|     INSERT INTO table_name [(column1, column2, ...)]                              |
|     SELECT colA, colB, ... FROM source_table [WHERE condition];                   |
|     *(CRITICAL: Do NOT use the VALUES keyword when inserting from a subquery!)*    |
+-----------------------------------------------------------------------------------+

1. The Column List and Positional Alignment

When writing an INSERT INTO table_name (columns...) VALUES (values...) statement, the specified column list determines the order and number of values that must appear in the VALUES clause.

-- Explicit Column List: Values correspond positionally to specified columns
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (301, 'Elena', 'Rostova', 'EROSTOVA', TO_DATE('2026-03-01', 'YYYY-MM-DD'), 'IT_PROG', 8500);

2. Omitting the Column List

If you omit the column list after the table name, Oracle requires you to provide a value for every column defined in the table, in the exact positional order in which the columns are stored in the data dictionary (COLUMN_ID in USER_TAB_COLUMNS).

-- Column list omitted: must supply values for ALL 4 DEPARTMENTS columns, in exact
-- physical schema order (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
INSERT INTO departments
VALUES (280, 'Quantum Computing', 103, 1700);

Exam Trap: If a table has 10 columns and you omit the column list, attempting to insert only 4 values raises ORA-00947: not enough values. Conversely, providing 11 values raises ORA-00913: too many values. In enterprise development, always specify the explicit column list to protect code from breaking when table columns are added, reordered, or dropped.

3. Handling NULL Values and Column Defaults

When inserting data, columns can receive NULL values or schema-defined default values through several distinct mechanisms:

+-----------------------------------------------------------------------------------+
|                         NULL AND DEFAULT INSERT BEHAVIORS                         |
|                                                                                   |
|  Mechanism 1: Explicit NULL literal in VALUES clause                              |
|               VALUES (302, 'David', NULL, 'DCHOU', SYSDATE, 'SA_REP', 5000)       |
|                                                                                   |
|  Mechanism 2: Omitting column from column list (No schema default defined)        |
|               -> Column automatically receives NULL (fails if NOT NULL constraint)|
|                                                                                   |
|  Mechanism 3: Omitting column from column list (Schema DEFAULT defined)           |
|               -> Column automatically receives the DEFAULT value                  |
|                                                                                   |
|  Mechanism 4: Explicit DEFAULT keyword in VALUES clause                           |
|               VALUES (303, 'Sara', 'Connor', 'SCONNOR', DEFAULT, 'HR_REP', 6000)  |
+-----------------------------------------------------------------------------------+

-- Demonstrating DEFAULT keyword and omitted columns
-- Assume table ORDERS has columns: ORDER_ID (PK), ORDER_DATE DEFAULT SYSDATE, STATUS DEFAULT 'PENDING'

-- Example A: Explicit DEFAULT keyword
INSERT INTO orders (order_id, order_date, status)
VALUES (5001, DEFAULT, 'SHIPPED');

-- Example B: Column omitted from list receives DEFAULT value
INSERT INTO orders (order_id, status)
VALUES (5002, 'PROCESSING'); -- ORDER_DATE receives SYSDATE automatically

-- Example C: Explicit NULL overrides column DEFAULT
INSERT INTO orders (order_id, order_date, status)
VALUES (5003, NULL, 'CANCELLED'); -- ORDER_DATE is stored as NULL, NOT SYSDATE!

4. Inserting Rows with Subqueries (Batch Inserts)

To copy or aggregate records from other tables, Oracle allows embedding a SELECT statement directly inside the INSERT statement.

-- Populating an archive table using a subquery
INSERT INTO retired_employees (emp_id, full_name, termination_date, final_salary)
SELECT 
    employee_id,
    first_name || ' ' || last_name,
    SYSDATE,
    salary
FROM employees
WHERE hire_date < TO_DATE('2010-01-01', 'YYYY-MM-DD');

Strict Subquery INSERT Rules Tested on 1Z0-071:

1. No VALUES Keyword: The VALUES keyword must never be used when inserting via a subquery. Writing INSERT INTO target_table VALUES (SELECT * FROM source_table) produces ORA-00936: missing expression or syntax errors.
2. Degree and Datatype Compatibility: The number of expressions in the SELECT list must match the number of target columns, and corresponding expressions must have compatible datatype families.

---

The UPDATE Statement

The UPDATE statement modifies existing column values in one or more rows of a table.

+-----------------------------------------------------------------------------------+
|                                   UPDATE SYNTAX                                   |
|                                                                                   |
|  UPDATE table_name [alias]                                                        |
|  SET column1 = expression1 [, column2 = expression2, ...]                         |
|  [WHERE filter_condition];                                                        |
+-----------------------------------------------------------------------------------+

-- Standard single-table update with WHERE clause
UPDATE employees
SET salary = salary * 1.10,
    commission_pct = NVL(commission_pct, 0) + 0.05
WHERE department_id = 80
  AND salary < 10000;

Critical Warning: If the WHERE clause is omitted, Oracle updates every single row in the table! Always verify whether an update condition is intended on the exam.

Updating Multiple Columns with Subqueries

Oracle SQL provides two powerful techniques for updating columns dynamically using subqueries:

Pattern 1: Updating Individual Columns with Scalar Subqueries

Each column in the SET clause can be assigned the result of an independent scalar subquery (a subquery returning at most one row and one column):

UPDATE employees e
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id),
    job_id = (SELECT job_id FROM jobs WHERE job_title = 'Senior Developer')
WHERE employee_id = 105;

Pattern 2: Multi-Column Correlated Subquery Update

Oracle allows updating multiple columns simultaneously in a single assignment by enclosing the target columns and the subquery in parentheses:

-- Updating multiple columns simultaneously using a single subquery
UPDATE employees e
SET (salary, job_id) = (
    SELECT max_salary, job_id 
    FROM jobs 
    WHERE job_title = 'Administration Vice President'
)
WHERE employee_id = 101;

+-----------------------------------------------------------------------------------+
|                         MULTI-COLUMN SUBQUERY UPDATE FLOW                         |
|                                                                                   |
|  UPDATE target_table t                                                            |
|  SET (t.col1, t.col2) = (SELECT s.colA, s.colB FROM source_table s WHERE ...)     |
|  WHERE [optional filter];                                                         |
|                                                                                   |
|  1. Subquery must project EXACTLY as many expressions as columns in (col1, col2). |
|  2. Subquery must return AT MOST ONE row per updated target row (scalar output).  |
|  3. If subquery returns 0 rows, Oracle assigns NULL to both col1 and col2!        |
|  4. If subquery returns > 1 row, Oracle raises ORA-01427: single-row subquery ... |
+-----------------------------------------------------------------------------------+

Exam Trap (The 0-Row Subquery NULL Trap): If the subquery in an UPDATE ... SET col = (SELECT ...) returns zero rows for a given updated record, Oracle does not leave the existing column value intact—it sets the target column to NULL! If that column has a NOT NULL constraint, Oracle raises ORA-01407: cannot update (...) to NULL.

---

The DELETE Statement

The DELETE statement removes one or more rows from a table or updatable view.

+-----------------------------------------------------------------------------------+
|                                   DELETE SYNTAX                                   |
|                                                                                   |
|  DELETE [FROM] table_name [alias]                                                 |
|  [WHERE filter_condition];                                                        |
+-----------------------------------------------------------------------------------+

-- Deleting specific rows using a subquery condition
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

Key Syntactic and Operational Rules of DELETE:

1. Optional FROM Keyword: In Oracle SQL, the FROM keyword is optional. Both DELETE FROM employees WHERE ... and DELETE employees WHERE ... are syntactically valid.
2. No Column Specification: DELETE removes entire rows; you cannot specify individual columns in a DELETE statement. (To clear a single column's value, use UPDATE table SET column_name = NULL).
3. Omitting the WHERE Clause: Omitting the WHERE clause deletes all rows from the table (DELETE FROM employees;), but the table structure, constraints, indexes, and privileges remain fully intact.

---

Deep Architectural Comparison: DELETE vs. TRUNCATE

A central topic on the Oracle 1Z0-071 examination is the technical and architectural comparison between the DELETE (DML) statement and the TRUNCATE (DDL) statement.

+-----------------------------------------------------------------------------------+
|                        STORAGE AND EXECUTION COMPARISON                           |
|                                                                                   |
|  DELETE Statement (DML):                                                          |
|  [Row 1] -> [Generate Undo/Redo] -> [Delete Mark] -> [Fire Trigger] -> [HWM Static|
|  [Row 2] -> [Generate Undo/Redo] -> [Delete Mark] -> [Fire Trigger] -> [HWM Static|
|  (Table Extents remain allocated; High Water Mark does NOT move back)             |
|                                                                                   |
|  TRUNCATE Statement (DDL):                                                        |
|  [Implicit Commit] -> [Deallocate Data Extents] -> [Reset HWM to 0] -> [Imp Commit|
|  (Zero undo generated for rows; DML triggers bypassed; Instantaneous execution)   |
+-----------------------------------------------------------------------------------+

1. The High Water Mark (HWM) Mechanics

In Oracle Database storage architecture, the High Water Mark (HWM) is a boundary marker within a segment's data blocks that indicates the highest block ever formatted to hold data:

• DELETE: When rows are deleted via DELETE, Oracle marks individual row slots as deleted inside the data blocks, but the physical blocks remain allocated to the table segment and the HWM is NOT reset. Subsequent Full Table Scans (FTS) must continue scanning all blocks up to the HWM, even if the table now contains 0 rows!
• TRUNCATE: When TRUNCATE TABLE is executed, Oracle resets the HWM back to the initial segment extent (or zero). Subsequent Full Table Scans immediately see an empty table and complete in 1 I/O operation.

2. Referential Integrity and Foreign Key Differences

A critical exam objective is understanding how foreign keys constrain DELETE vs TRUNCATE:

• DELETE: Evaluates foreign keys row-by-row. If a parent table row is referenced by child records, the delete succeeds if the constraint was defined with ON DELETE CASCADE or ON DELETE SET NULL. If no ON DELETE clause was specified, Oracle applies its default restrict behavior and raises ORA-02292: integrity constraint violated - child record found. Note that Oracle does not implement the ON DELETE RESTRICT or ON DELETE NO ACTION keywords — omitting the clause is the restrict behavior.
• TRUNCATE: TRUNCATE is a segment-level DDL operation and cannot evaluate row-by-row triggers or cascading actions. If a table's primary key or unique key is referenced by an enabled foreign key constraint in another table, TRUNCATE always fails immediately with ORA-02266: unique/primary keys in table referenced by enabled foreign keys—even if the child table contains zero rows!

To Truncate a Referenced Parent Table: You must either disable/drop the foreign key constraint on the child table first, or truncate the child table and then truncate the parent.

---

Side-by-Side Comparison: DELETE vs. TRUNCATE