Data Manipulation Language (DML)

If DDL (Data Definition Language) is about building the warehouse — creating shelves, labeling sections — then DML is about putting things on those shelves, moving them around, and throwing things away. DML is the set of SQL commands you use every day to work with the actual data inside your tables.

The four core DML commands are:

INSERT — Adding New Rows

Single-Row INSERT

The simplest form adds one row at a time. You list the column names, then the corresponding values:

-- Always list column names explicitly — safer and self-documenting
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id)
VALUES (207, 'Priya', 'Sharma', 'PSHARMA', 72000, 60);

Why list column names? If you skip them, you must provide values for every column in the exact order the table was created. That's fragile — if someone adds a column later, your INSERT breaks. Listing columns explicitly is much safer.

-- This works but is dangerous — depends on exact column order
-- Any structural change to the table will break this
INSERT INTO employees
VALUES (208, 'Marcus', 'Bell', 'MBELL', 'MBELL@company.com', '555-0100',
        '1234567890', 'IT_PROG', 68000, NULL, 103, 60);

Inserting with NULL Values

NULL means "unknown" or "not applicable". You can insert NULL explicitly or by simply omitting the column:

-- Explicit NULL for commission_pct (this employee has no commission)
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id, commission_pct)
VALUES (209, 'Ana', 'Costa', 'ACOSTA', 55000, 50, NULL);

-- Omitting the column achieves the same result if the column allows NULL
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id)
VALUES (209, 'Ana', 'Costa', 'ACOSTA', 55000, 50);
-- commission_pct will automatically be NULL

Inserting with DEFAULT Values

If a column has a default value defined in the table schema, you can use the DEFAULT keyword or simply omit the column:

-- hire_date has DEFAULT SYSDATE defined on the table
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id, hire_date)
VALUES (210, 'Tom', 'Reed', 'TREED', 61000, 80, DEFAULT);
-- hire_date will be set to today's date automatically

-- Or just omit it — same result:
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id)
VALUES (210, 'Tom', 'Reed', 'TREED', 61000, 80);

Multi-Row INSERT

Standard SQL / PostgreSQL / MySQL let you insert multiple rows in a single statement:

-- PostgreSQL / MySQL syntax — multiple rows separated by commas
INSERT INTO departments (department_id, department_name, location_id)
VALUES
    (280, 'Analytics', 1700),
    (290, 'DevOps',    1700),
    (300, 'Security',  1800);

-- Oracle: insert multiple rows with INSERT ALL
INSERT ALL
    INTO departments (department_id, department_name, location_id) VALUES (280, 'Analytics', 1700)
    INTO departments (department_id, department_name, location_id) VALUES (290, 'DevOps', 1700)
    INTO departments (department_id, department_name, location_id) VALUES (300, 'Security', 1800)
SELECT 1 FROM DUAL;
-- The SELECT 1 FROM DUAL at the end is required Oracle syntax

INSERT INTO ... SELECT (Copying Data Between Tables)

One of the most powerful INSERT forms — use a SELECT query as the source of data. The SELECT can include WHERE filters, JOINs, calculated columns, and aggregations:

-- Copy all IT department employees into an archive table
INSERT INTO employees_archive (employee_id, first_name, last_name, salary, archive_date)
SELECT employee_id, first_name, last_name, salary, SYSDATE
FROM   employees
WHERE  department_id = 60;

The SELECT can be as complex as you need:

-- Build a salary summary table from live data
INSERT INTO dept_salary_summary (department_id, avg_salary, headcount, snapshot_date)
SELECT department_id,
       ROUND(AVG(salary), 2),
       COUNT(*),
       TRUNC(SYSDATE)           -- today's date, time stripped
FROM   employees
GROUP BY department_id;

UPDATE — Modifying Existing Rows

UPDATE changes data that already exists in the table. Think of it like editing a cell in a spreadsheet — you find the row you want, then change one or more column values.

Single-Column UPDATE

-- Give employee 103 a 10% raise
UPDATE employees
SET    salary = salary * 1.10
WHERE  employee_id = 103;

-- Result: if salary was 90000, it becomes 99000

Multi-Column UPDATE

Separate multiple column assignments with commas inside the SET clause:

-- Promote employee 107: new title, new salary, new hire date recorded
UPDATE employees
SET    salary    = 78000,
       job_id    = 'IT_PROG',
       hire_date = DATE '2024-01-15'
WHERE  employee_id = 107;

UPDATE with a Subquery in SET

You can compute the new value using a subquery — useful when the new value depends on data from another table or from an aggregate:

-- Set each IT department employee's salary to their department's average
UPDATE employees e
SET    salary = (
    SELECT ROUND(AVG(salary), 2)
    FROM   employees
    WHERE  department_id = e.department_id   -- correlated: uses outer row's dept
)
WHERE  department_id = 60;
-- Each row gets updated with the average for department 60

UPDATE Filtered by a Subquery in WHERE

Use a subquery in the WHERE clause to target rows based on data in another table:

-- Give all employees in the 'IT' department a 5% pay increase
UPDATE employees
SET    salary = salary * 1.05
WHERE  department_id = (
    SELECT department_id
    FROM   departments
    WHERE  department_name = 'IT'
);

UPDATE with JOIN — Oracle Inline View Method

When you need values from another table for the SET clause, Oracle supports updating through an inline view:

-- Oracle: update using data from a joined table via inline view
UPDATE (
    SELECT e.salary,
           s.new_salary
    FROM   employees e
    JOIN   salary_adjustments s ON e.employee_id = s.employee_id
)
SET salary = new_salary;

-- CATASTROPHIC: this updates ALL 107 employees' salaries to 50000
UPDATE employees
SET salary = 50000;
-- No undo unless you're inside an uncommitted transaction!

-- Step 1: confirm the target rows
SELECT employee_id, salary FROM employees WHERE department_id = 90;

-- Step 2: now safe to update
UPDATE employees SET salary = 50000 WHERE department_id = 90;

DELETE — Removing Rows

DELETE removes rows from a table permanently (within the current transaction). Like UPDATE, always use a WHERE clause to target specific rows.

DELETE with WHERE

-- Delete a specific employee by ID
DELETE FROM employees
WHERE employee_id = 207;

-- Delete all employees whose department no longer exists
DELETE FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM departments
);

DELETE Without WHERE — Clears the Entire Table

-- Deletes ALL rows — the table structure (columns, constraints) remains intact
DELETE FROM temp_staging;

This is valid but slow on large tables, because every deleted row is individually logged so that a ROLLBACK is possible.

DELETE vs TRUNCATE — Understanding the Difference

-- TRUNCATE: instant but irreversible — no WHERE clause allowed
TRUNCATE TABLE temp_staging;

-- DELETE: slow but safe — can be rolled back before COMMIT
DELETE FROM temp_staging;
ROLLBACK;  -- all rows come back

MERGE — The Upsert Operation

MERGE solves a very common problem: "Insert this row if it doesn't exist; update it if it does." This is known as an upsert (a portmanteau of update + insert).

The Real-World Problem MERGE Solves

Imagine you receive a nightly data feed of employee records from HR software. For each record:

• If the employee already exists in your database → update their details
• If they're new → insert them

Without MERGE, you'd need to check existence first, then branch into INSERT or UPDATE. MERGE handles this in a single, atomic statement.

Oracle MERGE Syntax

MERGE INTO employees e                      -- Target: the table to insert/update into
USING (                                     -- Source: where the new data comes from
    SELECT 207        AS employee_id,
           'Priya'    AS first_name,
           'Sharma'   AS last_name,
           75000      AS salary,
           60         AS department_id
    FROM DUAL
) src
ON (e.employee_id = src.employee_id)        -- Match condition: how to find existing rows

WHEN MATCHED THEN                           -- Row already exists in target
    UPDATE SET
        e.salary        = src.salary,
        e.department_id = src.department_id

WHEN NOT MATCHED THEN                       -- Row does not exist in target
    INSERT (employee_id, first_name, last_name, salary, department_id)
    VALUES (src.employee_id, src.first_name, src.last_name, src.salary, src.department_id);

MERGE with a Staging Table (Common Real-World Pattern)

-- Nightly sync: merge staging data into production
MERGE INTO employees e
USING employees_staging src
ON (e.employee_id = src.employee_id)

WHEN MATCHED THEN
    UPDATE SET
        e.salary        = src.salary,
        e.job_id        = src.job_id,
        e.department_id = src.department_id,
        e.updated_at    = SYSTIMESTAMP

WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
    VALUES (src.employee_id, src.first_name, src.last_name, src.email,
            src.hire_date, src.job_id, src.salary, src.department_id);

MERGE with DELETE (Oracle Extension)

Oracle allows deleting matched rows as part of the MERGE:

MERGE INTO employees e
USING hr_feed src
ON (e.employee_id = src.employee_id)

WHEN MATCHED THEN
    UPDATE SET e.salary = src.salary
    DELETE WHERE src.employment_status = 'TERMINATED';
    -- Terminated employees are deleted; active ones are updated

Transactions — Keeping Data Consistent

A transaction is a group of SQL statements that execute as a single unit of work. The golden rule: either all statements succeed and are saved, or none of them are.

The Bank Transfer Analogy

Imagine transferring $500 between bank accounts:

1. Deduct $500 from Account A
2. Add $500 to Account B

If step 1 succeeds but step 2 fails (power cut, server crash), $500 has vanished. Transactions prevent this by ensuring both steps happen together or neither does.

BEGIN / COMMIT / ROLLBACK

-- PostgreSQL / MySQL: explicitly start a transaction
BEGIN;

-- Step 1: debit the sender
UPDATE bank_accounts
SET    balance = balance - 500
WHERE  account_id = 1001;

-- Step 2: credit the receiver
UPDATE bank_accounts
SET    balance = balance + 500
WHERE  account_id = 1002;

-- Both succeeded — make the changes permanent
COMMIT;

-- If anything goes wrong before COMMIT:
ROLLBACK;  -- Both changes are undone; balances return to original values

SAVEPOINT — Partial Rollback

You can set checkpoints within a transaction and roll back to them without losing everything:

-- Oracle / PostgreSQL savepoints
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 60;
SAVEPOINT after_dept60_raise;           -- mark this point

UPDATE employees SET salary = salary * 1.08 WHERE department_id = 80;
-- Realized the 8% for dept 80 was wrong

ROLLBACK TO SAVEPOINT after_dept60_raise;
-- Dept 80 change is undone; dept 60 raise is still pending

UPDATE employees SET salary = salary * 1.04 WHERE department_id = 80;  -- correct rate
COMMIT;  -- commits both dept 60 (5%) and dept 80 (4%) raises

Oracle Transaction Behavior

In Oracle, you do not write BEGIN. A transaction starts implicitly with the first DML statement after the previous COMMIT or ROLLBACK:

-- Transaction begins automatically here
INSERT INTO departments (department_id, department_name) VALUES (310, 'Test Dept');
UPDATE departments SET department_name = 'Test Department' WHERE department_id = 310;

-- Permanently save both changes
COMMIT;

-- Or undo both:
ROLLBACK;

Autocommit Mode

Many database GUI tools (SQL Developer, DBeaver, DataGrip) operate in autocommit mode by default, where every DML statement is immediately and permanently committed.

-- SQL*Plus: check and control autocommit
SHOW AUTOCOMMIT;          -- shows current setting
SET AUTOCOMMIT OFF;       -- transactions require explicit COMMIT
SET AUTOCOMMIT ON;        -- every statement auto-commits (dangerous!)

-- MySQL:
SET autocommit = 0;       -- turn off autocommit
SET autocommit = 1;       -- turn on autocommit

RETURNING Clause — Get Back What You Changed

The RETURNING clause lets you retrieve column values from rows affected by INSERT, UPDATE, or DELETE — without needing a separate SELECT query afterwards. This is especially useful for getting auto-generated IDs or confirming changed values.

Oracle RETURNING INTO (PL/SQL)

In Oracle, RETURNING INTO is used inside PL/SQL blocks:

DECLARE
    v_new_salary  employees.salary%TYPE;
BEGIN
    UPDATE employees
    SET    salary = salary * 1.10
    WHERE  employee_id = 107
    RETURNING salary INTO v_new_salary;   -- capture the new value
    
    DBMS_OUTPUT.PUT_LINE('Updated salary: ' || v_new_salary);
    -- Output: Updated salary: 84700 (if old salary was 77000)
    COMMIT;
END;
/

PostgreSQL RETURNING (Plain SQL)

PostgreSQL supports RETURNING directly in SQL — no procedural code needed:

-- Get the auto-generated ID after insert
INSERT INTO employees (first_name, last_name, salary)
VALUES ('New', 'Employee', 65000)
RETURNING employee_id, salary;

-- Returns:
-- employee_id | salary
-- ------------|--------
-- 211         | 65000

-- See which rows were actually deleted
DELETE FROM employees
WHERE  hire_date < DATE '2000-01-01'
RETURNING employee_id, first_name, last_name;
-- Lists every employee that was just deleted

Soft Deletes — Hiding Without Destroying

A hard delete (using the DELETE statement) permanently removes data. Sometimes you need to "remove" records logically while preserving them physically — for auditing, compliance, or the ability to restore.

The soft delete pattern adds an is_deleted flag column. Rows are never truly deleted; they're just marked as deleted and filtered out of normal queries.

Setting Up Soft Deletes

-- Add soft-delete columns to the table
ALTER TABLE employees ADD (
    is_deleted  NUMBER(1)  DEFAULT 0   NOT NULL,  -- 0 = active, 1 = deleted
    deleted_at  TIMESTAMP,
    deleted_by  VARCHAR2(100)
);

-- "Delete" an employee: mark it, don't remove it
UPDATE employees
SET    is_deleted = 1,
       deleted_at = SYSTIMESTAMP,
       deleted_by = USER           -- USER = current Oracle session username
WHERE  employee_id = 207;

Querying with Soft Deletes

Every query must now filter out deleted rows:

-- Only show active (non-deleted) employees
SELECT employee_id, first_name, last_name, salary
FROM   employees
WHERE  is_deleted = 0;             -- This filter must appear everywhere!

-- Restore a soft-deleted record
UPDATE employees
SET    is_deleted = 0,
       deleted_at = NULL,
       deleted_by = NULL
WHERE  employee_id = 207;

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE is_deleted = 0;

-- Now all queries are simple and safe:
SELECT * FROM active_employees;   -- always excludes deleted rows

Audit Columns — Who Did What and When

Audit columns track when a row was created or last modified, and optionally by whom. They're standard practice in any production database.

Adding Audit Columns

CREATE TABLE employees (
    employee_id   NUMBER         PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    salary        NUMBER(10, 2),
    -- Audit columns:
    created_at    TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
    created_by    VARCHAR2(100)  DEFAULT USER         NOT NULL,
    updated_at    TIMESTAMP,
    updated_by    VARCHAR2(100)
);

Populating Audit Columns

-- On INSERT: created_at and created_by fill automatically via DEFAULT
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (211, 'Lin', 'Zhang', 69000);
-- created_at = now, created_by = current user — no extra code needed

-- On UPDATE: manually set the updated_ columns (or use a trigger)
UPDATE employees
SET    salary     = 72000,
       updated_at = SYSTIMESTAMP,
       updated_by = USER
WHERE  employee_id = 211;

Automating Audit Columns with a Trigger

Manually setting updated_at on every UPDATE is error-prone. A trigger does it automatically:

-- Oracle trigger: auto-update audit columns on every UPDATE
CREATE OR REPLACE TRIGGER trg_employees_audit
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSTIMESTAMP;
    :NEW.updated_by := USER;
END;
/

-- Now this UPDATE automatically sets updated_at and updated_by:
UPDATE employees SET salary = 75000 WHERE employee_id = 211;
-- No need to manually set updated_at!

Complete DML Workflow — Putting It All Together

Here's a realistic scenario combining all DML operations within a transaction:

-- ============================================================
-- Scenario: Onboard a new hire, fill the open position
-- ============================================================

-- Transaction starts implicitly (Oracle) or with BEGIN (PostgreSQL)

-- 1. Insert the new employee
INSERT INTO employees (
    employee_id, first_name, last_name, email,
    hire_date,   job_id,     salary,   department_id, manager_id
)
VALUES (
    212, 'Sofia', 'Reyes', 'SREYES',
    TRUNC(SYSDATE), 'SA_REP', 58000, 80, 145
);

-- 2. Update the manager's headcount tracker
UPDATE managers_summary
SET    team_size  = team_size + 1,
       updated_at = SYSTIMESTAMP
WHERE  manager_id = 145;

-- 3. Close the job posting that this hire fills
UPDATE job_postings
SET    is_active   = 0,
       closed_date = TRUNC(SYSDATE),
       filled_by   = 212
WHERE  job_posting_id = 99;

-- 4. Everything succeeded — make all three changes permanent
COMMIT;

-- If any step above had failed, we would run:
-- ROLLBACK;
-- ...and all three statements would be reversed automatically

Common Errors

Interview Corner

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT debit_done;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- If second update fails:
ROLLBACK TO SAVEPOINT debit_done;   -- only second UPDATE is undone
-- If both succeed:
COMMIT;                             -- both changes made permanent

MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED    THEN UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN INSERT (id, salary) VALUES (s.id, s.salary);

-- Safe: use a subquery to deduplicate source
MERGE INTO employees t
USING (SELECT employee_id, MAX(salary) AS salary
       FROM staging_employees
       GROUP BY employee_id) s ON (t.employee_id = s.employee_id)
...

Related Topics

• DDL Commands — CREATE, ALTER, TRUNCATE — the table structures DML operates on
• Constraints — constraints that DML must satisfy (NOT NULL, FK, UNIQUE, CHECK)
• Transactions & Locks — COMMIT, ROLLBACK, isolation levels, and lock behaviour
• Indexes — DML performance is affected by the number of indexes on a table