INSERT, UPDATE & DELETE (DML)

Data Manipulation Language (DML)

DML covers the three statements that change the data inside existing tables:

Statement	Action
INSERT	Adds new rows
UPDATE	Modifies existing rows
DELETE	Removes existing rows

Before any UPDATE or DELETE: Run the equivalent SELECT with the same WHERE condition first. Confirm you're targeting exactly the right rows. Then run the modification.

Sample Table

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    department TEXT    NOT NULL,
    salary     REAL    NOT NULL,
    hire_date  TEXT    NOT NULL,
    is_active  INTEGER DEFAULT 1
);

INSERT

Insert a Single Row

-- Specify column names and values (recommended form)
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Priya Sharma', 'Finance', 75000, '2022-03-15');

Specifying column names is the safe form:

• Columns not listed get their DEFAULT value (or NULL if no default)
• You can insert columns in any order
• Adding new columns to the table won't break existing inserts

Insert Without Column Names

-- Must supply values for ALL columns in the correct order
INSERT INTO employees
VALUES (NULL, 'Raj Patel', 'Technology', 92000, '2021-07-01', 1);
-- NULL for id → AUTOINCREMENT generates it

Avoid this form — it breaks silently if the table schema changes.

Insert Multiple Rows

INSERT INTO employees (name, department, salary, hire_date)
VALUES
    ('Meera Singh',  'Marketing',  68000, '2023-01-10'),
    ('Arjun Nair',   'Finance',    81000, '2020-11-22'),
    ('Sunita Rao',   'Technology', 88000, '2022-09-05'),
    ('Dev Kumar',    'Marketing',  72000, '2021-04-18');

Multi-row inserts are faster than running individual INSERT statements in a loop.

Insert From a SELECT (INSERT INTO … SELECT)

Copy data from one table (or query result) into another:

-- Copy all Finance employees into a separate archive table
INSERT INTO finance_employees (name, salary, hire_date)
SELECT name, salary, hire_date
FROM employees
WHERE department = 'Finance';

The SELECT can be any valid query — including joins, aggregates, or transformations.

INSERT OR REPLACE / UPSERT

Some databases support "upsert" — insert if not exists, update if it does:

-- SQLite
INSERT OR REPLACE INTO employees (id, name, department, salary, hire_date)
VALUES (1, 'Priya Sharma', 'Finance', 80000, '2022-03-15');

-- PostgreSQL (ON CONFLICT)
INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (1, 'Priya Sharma', 'Finance', 80000, '2022-03-15')
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

-- MySQL
INSERT INTO employees VALUES (1, 'Priya Sharma', 'Finance', 80000, '2022-03-15', 1)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

UPDATE

UPDATE modifies existing rows. Always include a WHERE clause — without it, every row in the table is updated.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Update a Single Column

-- Give Priya a raise
UPDATE employees
SET salary = 82000
WHERE id = 1;

Update Multiple Columns

-- Transfer and promote Dev Kumar
UPDATE employees
SET department = 'Finance',
    salary     = 79000
WHERE id = 6;

Update Based on a Calculation

-- Give everyone in Technology a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Technology';

Update Using a Subquery

-- Set everyone's salary to the Finance department average
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'Finance')
WHERE department = 'Marketing';

UPDATE with JOIN (MySQL / SQL Server)

-- MySQL: give employees a raise if their department budget > 400000
UPDATE employees e
JOIN departments d ON e.department = d.name
SET e.salary = e.salary * 1.08
WHERE d.budget > 400000;

-- PostgreSQL equivalent using FROM
UPDATE employees e
SET salary = e.salary * 1.08
FROM departments d
WHERE e.department = d.name
  AND d.budget > 400000;

Verify Before Updating

-- Step 1: SELECT to confirm the target rows
SELECT name, salary, department
FROM employees
WHERE department = 'Technology';

-- Step 2: If results look correct, run the UPDATE
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Technology';

-- Step 3: Verify the change
SELECT name, salary FROM employees WHERE department = 'Technology';

DELETE

DELETE removes rows. Always include a WHERE clause — without it, every row is deleted.

Syntax

DELETE FROM table_name
WHERE condition;

Delete a Single Row

DELETE FROM employees WHERE id = 8;

Delete Based on a Condition

-- Delete all inactive employees
DELETE FROM employees WHERE is_active = 0;

-- Delete employees hired before 2020
DELETE FROM employees WHERE hire_date < '2020-01-01';

Delete Based on a Subquery

-- Delete all Marketing employees who have no orders
DELETE FROM employees
WHERE department = 'Marketing'
  AND id NOT IN (SELECT DISTINCT employee_id FROM orders WHERE employee_id IS NOT NULL);

DELETE with JOIN (MySQL)

-- MySQL: delete employees in departments with budget below 250000
DELETE e
FROM employees e
JOIN departments d ON e.department = d.name
WHERE d.budget < 250000;

TRUNCATE — Delete All Rows Fast

TRUNCATE removes all rows from a table, much faster than DELETE (no row-by-row processing, no WHERE):

TRUNCATE TABLE temp_data;  -- Not available in SQLite (use DELETE FROM temp_data)

Differences between DELETE and TRUNCATE:

	DELETE	TRUNCATE
WHERE clause	Supported	Not supported
Speed	Slower (logs each row)	Fast
Auto-increment reset	No	Yes (in most databases)
Triggers fire	Yes	Usually not
Rollback	Yes	Not always

Transactions — Wrapping DML Safely

Wrap multiple DML statements in a transaction so they either all succeed or all fail:

BEGIN;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;

-- If both succeed:
COMMIT;

-- If something went wrong:
ROLLBACK;

Full transaction details are in Chapter 17.

Practical Examples

Example 1: Onboard a New Employee

INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Nisha Gupta', 'Technology', 87000, '2026-06-27');

Example 2: Annual Salary Review

-- 12% raise for high performers (salary > 80k)
UPDATE employees
SET salary = ROUND(salary * 1.12, 0)
WHERE salary > 80000;

-- 7% raise for everyone else
UPDATE employees
SET salary = ROUND(salary * 1.07, 0)
WHERE salary <= 80000;

Example 3: Soft Delete (Mark Inactive)

Instead of deleting records (losing history), mark them inactive:

-- Don't do this:
DELETE FROM employees WHERE id = 5;

-- Do this instead (preserve history):
UPDATE employees SET is_active = 0 WHERE id = 5;

Example 4: Archive Then Delete

-- Step 1: Archive old employees to history table
INSERT INTO employees_history
SELECT *, CURRENT_TIMESTAMP AS archived_at
FROM employees
WHERE hire_date < '2019-01-01';

-- Step 2: Remove them from the main table
DELETE FROM employees
WHERE hire_date < '2019-01-01';

Common Mistakes

1. UPDATE or DELETE without WHERE

-- CATASTROPHIC — updates every single row
UPDATE employees SET salary = 50000;

-- CATASTROPHIC — deletes all data
DELETE FROM employees;

-- Always add WHERE
UPDATE employees SET salary = 50000 WHERE id = 1;

2. Forgetting the column list in INSERT

-- Fragile — breaks if table structure changes
INSERT INTO employees VALUES (NULL, 'Ritu', 'HR', 65000, '2026-01-01', 1);

-- Robust — explicitly maps columns to values
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Ritu', 'HR', 65000, '2026-01-01');

3. NOT IN with NULLs in DELETE subquery

-- Dangerous — if subquery has NULLs, deletes nothing
DELETE FROM employees
WHERE id NOT IN (SELECT employee_id FROM orders);

-- Safe
DELETE FROM employees
WHERE id NOT IN (SELECT employee_id FROM orders WHERE employee_id IS NOT NULL);
-- Or use NOT EXISTS

4. Updating the wrong column

-- Wrong — updates 'id' instead of 'salary'
UPDATE employees SET id = 82000 WHERE name = 'Priya Sharma';

-- Correct
UPDATE employees SET salary = 82000 WHERE name = 'Priya Sharma';

Practice Exercises

1. Insert a new employee: your choice of name and department, salary 70,000, hired today.
2. Give all Marketing employees a 5% salary increase.
3. Change Meera Singh's department to Finance and update her salary to 74,000.
4. Delete any employees with a salary below 65,000.
5. Write a query to "soft delete" (set is_active = 0) for all employees hired before 2021.

Summary

In this chapter you learned:

• INSERT INTO table (cols) VALUES (...) — always specify column names
• Multi-row insert: one INSERT with multiple VALUES rows
• INSERT INTO ... SELECT ... — copy data from a query result
• UPDATE table SET col = val WHERE condition — always use WHERE
• DELETE FROM table WHERE condition — always use WHERE
• TRUNCATE — fast all-row deletion; not transactional in all databases
• Verify with SELECT before running UPDATE or DELETE
• Wrap related DML in a BEGIN / COMMIT transaction for safety
• Prefer soft delete (marking inactive) over hard delete for audit trails

Next up: CREATE, ALTER & DROP (DDL) — define and modify the structure of your tables.