Data Manipulation

INSERT, UPDATE, and DELETE operations.

Modifying Data in Databases

So far we've only read data with SELECT. Now we'll learn how to create, modify, and delete data. These operations, INSERT, UPDATE, and DELETE, are collectively known as DML (Data Manipulation Language). They're how you actually change what's stored in your database. Be careful: unlike SELECT, these operations permanently modify your data.

Starting Table: employees

We'll modify this table throughout the lesson:

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,   -- database assigns 1, 2, 3, ...
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary     INTEGER
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
    ('Alice',   'Johnson', 'Engineering',  95000),
    ('Bob',     'Smith',   'Marketing',    65000),
    ('Charlie', 'Brown',   'Engineering', 105000);

SELECT * FROM employees ORDER BY id;

Expected Output:

┌────┬────────────┬───────────┬─────────────┬────────┐
│ id │ first_name │ last_name │ department  │ salary │
├────┼────────────┼───────────┼─────────────┼────────┤
│  1 │ Alice      │ Johnson   │ Engineering │  95000 │
│  2 │ Bob        │ Smith     │ Marketing   │  65000 │
│  3 │ Charlie    │ Brown     │ Engineering │ 105000 │
└────┴────────────┴───────────┴─────────────┴────────┘

INSERT: Adding New Data

INSERT adds new rows to a table. You specify which columns to fill and what values to use.

Insert One Row

Add a single employee with all required fields.

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Diana', 'Martinez', 'Sales', 72000);

SELECT * FROM employees ORDER BY id;

Expected Output:

┌────┬────────────┬───────────┬─────────────┬────────┐
│ id │ first_name │ last_name │ department  │ salary │
├────┼────────────┼───────────┼─────────────┼────────┤
│  1 │ Alice      │ Johnson   │ Engineering │  95000 │
│  2 │ Bob        │ Smith     │ Marketing   │  65000 │
│  3 │ Charlie    │ Brown     │ Engineering │ 105000 │
│  4 │ Diana      │ Martinez  │ Sales       │  72000 │
└────┴────────────┴───────────┴─────────────┴────────┘

Diana got id 4 without us naming it: the SERIAL column pulled the next value from its sequence.

Insert Partial Data

You don't need to specify every column, omitted columns get NULL or their default values.

INSERT INTO employees (first_name, last_name)
VALUES ('Eve', 'Davis');

SELECT * FROM employees WHERE first_name = 'Eve';

Expected Output:

┌────┬────────────┬───────────┬────────────┬────────┐
│ id │ first_name │ last_name │ department │ salary │
├────┼────────────┼───────────┼────────────┼────────┤
│  5 │ Eve        │ Davis     │            │        │
└────┴────────────┴───────────┴────────────┴────────┘

department and salary are NULL. Note how psql prints NULL: as an empty cell, not the word "NULL". An empty string would look exactly the same, which is one reason IS NULL exists.

Insert Multiple Rows

Insert several rows in a single statement (more efficient).

INSERT INTO employees (first_name, last_name, department, salary)
VALUES
    ('Frank', 'Wilson', 'Marketing',   70000),
    ('Grace', 'Lee',    'Sales',       78000),
    ('Henry', 'Taylor', 'Engineering', 92000);

SELECT * FROM employees ORDER BY id;

Expected Output:

┌────┬────────────┬───────────┬─────────────┬────────┐
│ id │ first_name │ last_name │ department  │ salary │
├────┼────────────┼───────────┼─────────────┼────────┤
│  1 │ Alice      │ Johnson   │ Engineering │  95000 │
│  2 │ Bob        │ Smith     │ Marketing   │  65000 │
│  3 │ Charlie    │ Brown     │ Engineering │ 105000 │
│  4 │ Diana      │ Martinez  │ Sales       │  72000 │
│  5 │ Eve        │ Davis     │             │        │
│  6 │ Frank      │ Wilson    │ Marketing   │  70000 │
│  7 │ Grace      │ Lee       │ Sales       │  78000 │
│  8 │ Henry      │ Taylor    │ Engineering │  92000 │
└────┴────────────┴───────────┴─────────────┴────────┘

3 rows in one statement, one round trip to the server instead of three.

Insert from Another Table

Copy data from one table to another using SELECT.

-- Assumes an employees_archive table already exists with matching columns:
-- CREATE TABLE employees_archive (id INTEGER, first_name VARCHAR(50), last_name VARCHAR(50));
INSERT INTO employees_archive (id, first_name, last_name)
SELECT id, first_name, last_name
FROM employees
WHERE department = 'Sales';

Copies all Sales employees to the archive table

Column Order Matters: The VALUES must match the column order you specified. If you list columns as (name, salary), VALUES must be ('Alice', 95000), not (95000, 'Alice').

UPDATE: Modifying Existing Data

UPDATE changes values in existing rows. Always use a WHERE clause unless you want to update every row.

Update One Row

Give Alice a raise.

UPDATE employees
SET salary = 100000
WHERE id = 1;

Before / After:

Before:  │ 1  │ Alice      │ Johnson   │ Engineering │ 95000  │
After:   │ 1  │ Alice      │ Johnson   │ Engineering │ 100000 │ ← Updated

Update Multiple Columns

Bob gets promoted and a raise.

UPDATE employees
SET 
    department = 'Engineering',
    salary = 85000
WHERE id = 2;

Before / After:

Before:  │ 2  │ Bob        │ Smith     │ Marketing   │ 65000  │
After:   │ 2  │ Bob        │ Smith     │ Engineering │ 85000  │ ← Both changed

Update Multiple Rows

Give all Marketing employees a 10% raise.

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';

Result: All Marketing employees get 10% increase

Before:  Frank: $70,000
After:   Frank: $77,000 (70000 * 1.10)

Conditional Update

Update only if certain conditions are met.

UPDATE employees
SET salary = salary + 5000
WHERE department = 'Engineering' AND salary < 90000;

Only Engineering employees earning less than $90k get the bonus

⚠️ Critical Warning: Forgetting WHERE updates EVERY row!

-- DISASTER: Sets everyone's salary to 50000
UPDATE employees SET salary = 50000;

-- SAFE: Only updates specific employee
UPDATE employees SET salary = 50000 WHERE id = 2;

DELETE: Removing Data

DELETE removes rows from a table permanently. There's no undo button.

Delete One Row

Remove a specific employee by ID.

DELETE FROM employees
WHERE id = 5;

Before / After:

Before:  │ 5  │ Eve        │ Davis     │ NULL        │ NULL   │ ← Will be deleted (8 employees)
After:   Row removed, 7 employees remain

Delete Multiple Rows

Remove all employees from a specific department.

DELETE FROM employees
WHERE department = 'Sales';

Result: Removes Diana and Grace (both in Sales)

Deleted:
│ 4  │ Diana      │ Martinez  │ Sales       │ 72000  │ ✗
│ 7  │ Grace      │ Lee       │ Sales       │ 78000  │ ✗

Delete with Complex Condition

Remove employees meeting multiple criteria.

DELETE FROM employees
WHERE salary < 70000 AND department = 'Marketing';

Only removes Marketing employees earning less than $70k

Delete All Rows (Dangerous!)

Remove everything from the table (structure remains).

DELETE FROM employees;

Warning: Deletes all rows! Table is now empty.

Before:  8 rows
After:   0 rows (but table structure still exists)

🚨 Extreme Danger: DELETE without WHERE removes ALL rows!

Always test with SELECT first:

-- Step 1: TEST with SELECT
SELECT * FROM employees WHERE id = 5;

-- Step 2: Verify it returns what you expect

-- Step 3: THEN delete
DELETE FROM employees WHERE id = 5;

TRUNCATE: Fast Delete All

TRUNCATE removes all rows instantly, faster than DELETE, because it discards whole data files instead of marking rows dead one at a time. It has no WHERE clause: it is all or nothing.

-- Removes every row, but leaves the sequence where it was
TRUNCATE TABLE employees;

-- Add RESTART IDENTITY to also reset the id counter back to 1
TRUNCATE TABLE employees RESTART IDENTITY;

-- In PostgreSQL, TRUNCATE is transactional like any other statement:
BEGIN;
TRUNCATE TABLE employees;   -- table now looks empty
ROLLBACK;                   -- and every row is back

Immediately removes all rows. Note that plain TRUNCATE leaves sequences alone: you need RESTART IDENTITY to reset the id counter.

TRUNCATE vs DELETE

TRUNCATE: Much faster on big tables, all rows or nothing, no WHERE, needs RESTART IDENTITY to reset counters, takes an exclusive lock on the table
DELETE: Slower, can target rows with WHERE, fires row-level triggers, leaves dead rows for VACUUM to reclaim
Both can be rolled back in PostgreSQL: wrap either one in BEGIN and ROLLBACK undoes it

Portability warning: that last point is PostgreSQL-specific. In MySQL and Oracle, TRUNCATE is DDL and causes an implicit commit, so it cannot be rolled back. Do not rely on undoing a TRUNCATE unless you know your engine allows it.

RETURNING: Get Data Back

Some databases (PostgreSQL) let you see what was inserted, updated, or deleted.

INSERT with RETURNING

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 80000)
RETURNING id, first_name, salary;

Expected Output:

┌────┬────────────┬────────┐
│ id │ first_name │ salary │
├────┼────────────┼────────┤
│  9 │ John       │  80000 │
└────┴────────────┴────────┘

The id was generated by the sequence, so this is the only way to learn
it without issuing a second query.

UPDATE with RETURNING

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering'
RETURNING first_name, salary;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Alice      │ 104500 │
│ Charlie    │ 115500 │
│ Henry      │ 101200 │
└────────────┴────────┘

Three rows changed and you see all three, without a follow-up SELECT.
(95000 * 1.10 = 104500. salary is INTEGER, so the numeric result is
rounded back to a whole number on the way in.)

Best Practices & Safety

✅ Always Use WHERE

Unless you genuinely want to affect every row, always include a WHERE clause in UPDATE and DELETE statements.

✅ Test with SELECT First

Before UPDATE or DELETE, run a SELECT with the same WHERE clause to see what will be affected.

✅ Use Transactions

Wrap dangerous operations in BEGIN/COMMIT so you can ROLLBACK if something goes wrong.

✅ Backup Before Bulk Changes

Before running large UPDATEs or DELETEs on production, always have a recent backup.

❌ Never Run in Production Without Testing

Test all data manipulation queries on development or staging databases first.

Using Transactions for Safety

Transactions let you test changes before making them permanent.

-- Start transaction
BEGIN;

-- Make changes
UPDATE employees SET salary = salary * 1.20 WHERE department = 'Engineering';

-- Check if it looks correct
SELECT * FROM employees WHERE department = 'Engineering';

-- If good: make it permanent
COMMIT;

-- If bad: undo everything
ROLLBACK;

Changes aren't permanent until COMMIT. Use ROLLBACK to undo.

Key Takeaways

INSERT adds new rows to a table
UPDATE modifies existing rows - always use WHERE
DELETE removes rows - always use WHERE
TRUNCATE removes all rows quickly
Test with SELECT first before UPDATE or DELETE
Use transactions to safely test changes
RETURNING shows affected rows (PostgreSQL)
Data manipulation is permanent, there's no undo button, so always be cautious and test thoroughly

Back to "SQL Basics"

Database FundamentalsLesson 4

Next: "Joins & Relationships"