Lesson 10A – Managing Tables Using DML Statements in Oracle (or: how to change the data without regretting everything)

And look, reading data is safe. It’s like browsing a store: you can stare at products all day and nothing changes. DML is when you start rearranging shelves, throwing things out, and repainting the walls—except the “walls” are shared by everyone and the consequences are permanent unless you manage transactions correctly.

This lesson is about making changes on purpose and being able to undo them when you inevitably change the wrong thing.

You will learn to:

• Describe each DML statement: INSERT, UPDATE, DELETE, and (later) MERGE.
• Insert new rows into tables.
• Update and delete existing rows safely.
• Understand TRUNCATE vs DELETE.
• Control transactions with COMMIT, ROLLBACK, and SAVEPOINT.
• Use SELECT ... FOR UPDATE to lock rows while you’re editing them.

1. DML and Transactions: What’s at Stake

DML (Data Manipulation Language) statements:

• INSERT – add new rows.
• UPDATE – modify existing rows.
• DELETE – remove rows.
• MERGE – conditional insert/update/delete (you’ll meet this later).

A transaction is a set of DML statements that together form a logical unit of work, for example:

• Insert a new employee.
• Update their department’s headcount.
• Log the change in an audit table.

You either want all of that to succeed, or none of it. That’s what COMMIT and ROLLBACK are for.

2. INSERT – Getting New Rows into a Table

2.1 Basic INSERT ... VALUES

Best practice: name the columns, then provide matching values.

INSERT INTO departments (department_id, department_name, manager_id, location_id)
    VALUES (70, 'Public Relations', 100, 1700);

Rules:

• The column list in parentheses defines the target columns.
• The VALUES list is positional: first value → first column, etc.
• Character and date literals need single quotes ('Public Relations').
• Number literals do not (100, 1700).

You can omit the column list only if you provide values for every column in the table, in the table’s default column order:

INSERT INTO departments
    VALUES (80, 'Sales', 149, 2500);

This is legal but fragile—if a column is added or reordered, this breaks.

2.2 Inserting NULL values

Two ways to get NULL into a column:

• Implicitly: leave the column off the column list.

  INSERT INTO demo (id)
      VALUES (2);   -- name column becomes NULL

• Explicitly: use the NULL keyword.

  INSERT INTO departments (department_id, department_name, manager_id, location_id)
      VALUES (100, 'Finance', NULL, NULL);

Works as long as the column does not have a NOT NULL constraint.

2.3 Inserting dates and special values

Use date functions or TO_DATE to avoid ambiguity:

INSERT INTO employees (employee_id, last_name, hire_date, salary)
    VALUES (300, 'Nguyen', CURRENT_DATE, 5000);
    
    INSERT INTO employees (employee_id, last_name, hire_date, salary)
    VALUES (301, 'Lee', TO_DATE('2016-02-01', 'YYYY-MM-DD'), 4500);

• CURRENT_DATE – evaluated from the session time zone.
• SYSDATE – evaluated at the database server.

2.4 INSERT with a subquery (INSERT ... SELECT)

You can insert multiple rows at once by selecting from another table.

INSERT INTO sales_reps (id, name, salary, commission_pct)
    SELECT employee_id,
           last_name,
           salary,
           commission_pct
    FROM   employees
    WHERE  job_id = 'SA_REP';

• No VALUES clause here.
• The target column list in sales_reps must line up positionally and by type with the SELECT list.

3. UPDATE – Changing Existing Rows (Carefully)

UPDATE modifies existing rows. The dangers live mostly in the WHERE clause.

3.1 Basic UPDATE

UPDATE employees
    SET    department_id = 50
    WHERE  employee_id   = 113;

• Only employee 113 is moved to department 50.

If you omit the WHERE clause:

UPDATE employees
    SET    department_id = 10;

• Every row in employees gets department 10.
• There’s an entire genre of “I forgot the WHERE clause” horror stories; don’t contribute to it.

3.2 Updating multiple columns

UPDATE employees
    SET    salary   = 6000,
           job_id   = 'SA_REP'
    WHERE  employee_id = 113;

You can even use subqueries to copy values from another row:

-- Make employee 103’s job and salary match employee 205
    UPDATE employees
    SET   (job_id, salary) = (
            SELECT job_id, salary
            FROM   employees
            WHERE  employee_id = 205
          )
    WHERE employee_id = 103;

Be sure the subquery returns exactly one row.

4. DELETE and TRUNCATE – Removing Rows

4.1 DELETE

DELETE removes rows from a table.

DELETE FROM departments
    WHERE  department_name = 'Finance';

• Removes only rows where the condition matches.

Without a WHERE clause:

DELETE FROM demo;

• Removes all rows from demo, but the table structure stays.
• Can be undone with ROLLBACK if you haven’t committed yet.

You can use subqueries to drive deletions:

DELETE FROM employees
    WHERE  department_id IN (
             SELECT department_id
             FROM   departments
             WHERE  department_name LIKE 'Public%'
           );

4.2 TRUNCATE

TRUNCATE is like DELETE without a WHERE clause, but more final and much faster.

TRUNCATE TABLE demo;

• Removes all rows.
• Keeps the table structure.
• Is a DDL operation → it auto‑commits.
• Cannot be rolled back, even if you try.

Why use it?

• DELETE logs each row change (slow for large tables).
• TRUNCATE typically does not log each row, so it’s much faster—but permanent.

5. Transaction Control: COMMIT, ROLLBACK, SAVEPOINT

A transaction starts with the first DML after the last COMMIT/ROLLBACK and ends when you:

• Issue COMMIT – make all changes permanent.
• Issue ROLLBACK – undo all changes since the last commit.
• Execute a DDL or DCL statement (CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE), which auto‑commits.
• Exit SQL Developer / SQL*Plus normally (commit) or crash (rollback).

5.1 COMMIT

INSERT INTO demo VALUES (1, 'Mickey');
    INSERT INTO demo VALUES (2, 'Mary');
    COMMIT;

Effects:

• Changes are now visible to all sessions.
• Locks are released.
• Savepoints (if any) are discarded.

5.2 ROLLBACK

INSERT INTO demo VALUES (3, 'Larry');
    ROLLBACK;

Effects:

• Undoes all uncommitted changes back to the last commit.
• Data returns to its previous state.
• Locks from those changes are released.

Rollbacks have no effect on changes that have already been committed.

5.3 SAVEPOINT

SAVEPOINT lets you set intermediate markers within a transaction.

Example:

INSERT INTO demo VALUES (1, 'Mickey');
    INSERT INTO demo VALUES (2, 'Mary');
    SAVEPOINT before_mass_update;
    
    UPDATE demo
    SET    name = 'Bentley';   -- oops, too broad
    
    ROLLBACK TO before_mass_update;

• Rows inserted before the savepoint remain part of the transaction.
• The mass UPDATE is undone.
• You can still COMMIT or ROLLBACK the whole transaction later.

Multiple savepoints are allowed, but rolling back to an earlier savepoint discards any savepoints set after it.

5.4 Implicit vs explicit transaction boundaries

Implicit commits happen when you execute:

• DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT.
• DCL: GRANT, REVOKE.
• Normal session exit.

Implicit rollbacks happen when:

• The session crashes or disconnects abnormally.

Until you commit:

• Only your session sees its uncommitted changes.
• Affected rows are locked against conflicting writes from other sessions.

6. Row Locking and SELECT ... FOR UPDATE

Oracle uses row‑level locking:

• Readers don’t block readers.
• Readers don’t block writers.
• Writers block other writers on the same rows.

To explicitly lock rows while you inspect them, you can use:

SELECT *
    FROM   demo
    FOR UPDATE;

Effects:

• Locks the selected rows until you COMMIT or ROLLBACK.
• Another session trying to update those rows will block until your lock is released.

You can limit locking to certain columns/tables in joins:

SELECT e.employee_id,
           e.salary,
           d.department_name
    FROM   employees e
    JOIN   departments d
           ON e.department_id = d.department_id
    FOR UPDATE OF e.salary;

• Only rows in employees (where salary resides) are locked.

6.1 FOR UPDATE with WAIT

If another session already holds a lock, your FOR UPDATE will wait indefinitely—unless you set a timeout:

SELECT *
    FROM   demo
    FOR UPDATE WAIT 5;

• Oracle waits up to 5 seconds to acquire the lock.
• If it can’t, you get an error like “resource busy, acquire with WAIT timeout expired”.

This prevents your session from hanging forever because someone went to lunch holding a lock.

6.2 LOCK TABLE

LOCK TABLE lets you explicitly lock one or more tables in a specific mode:

LOCK TABLE demo IN EXCLUSIVE MODE;

• Other sessions may be prevented from reading/writing depending on the lock mode.
• Locks are released on COMMIT or ROLLBACK.

Use this sparingly; row‑level locks are usually enough.

7. Read Consistency – What Other Sessions See

Oracle guarantees that queries see a consistent snapshot of data:

• Readers do not see uncommitted changes from other sessions.
• A session that updates a row sees its own changes immediately.

Example:

• Session A: UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 200; (uncommitted).
• Session B: SELECT salary FROM employees WHERE employee_id = 200;

Session B sees the old salary until Session A commits. This avoids “partial” views of data mid‑transaction.

8. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

• Use INSERT, UPDATE, and DELETE to manipulate data safely.
• Use TRUNCATE when you truly mean “empty this table, quickly and permanently”.
• Group related changes into transactions and control them with COMMIT, ROLLBACK, and SAVEPOINT.
• Understand when changes become visible to other sessions and when locks are held.
• Use SELECT ... FOR UPDATE (with optional WAIT) to safely edit data without conflicting writers.

You’re now trusted not just to read from the database, but to change it—and more importantly, to back out your mistakes before everyone else sees them.