INSERT, UPDATE, DELETE: Modifying Data
Reads are forgiving. Writes are forever. In 2017 a GitLab engineer typed
rm -rf on the wrong server and wiped 300GB of production data. The SQL equivalent is DELETE FROM users without a WHERE. This lesson teaches you the syntax, and the muscle memory that prevents disaster.Learning Objectives
After this lesson, you will be able to:
- Use INSERT INTO to add single and multiple rows to a table
- Use UPDATE SET WHERE to modify existing rows safely
- Use DELETE FROM WHERE to remove rows without dropping the table
- Explain why WHERE clauses are critical for UPDATE and DELETE statements
- Wrap multiple changes in a transaction with BEGIN, COMMIT, and ROLLBACK
- Use INSERT OR REPLACE / INSERT OR IGNORE / ON CONFLICT (Postgres UPSERT) for conflict handling
This lesson covers the "write" side of SQL. If SELECT is reading a book, INSERT/UPDATE/DELETE is writing, editing, and erasing pages. The good news: the syntax is simpler than SELECT. The catch: mistakes are permanent (unless you use transactions). Let's learn to modify data safely.
#INSERT — Adding New Rows
#Basic INSERT
sql
-- Insert a single row
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES ('Alice Chen', 3, 85000, '2024-01-15');
-- Insert with all columns (must match table column order exactly)
INSERT INTO employees
VALUES (NULL, 'Bob Smith', 2, 72000, '2024-02-01');
-- NULL for auto-increment id
#Inserting Multiple Rows
sql
-- Insert multiple rows in one statement (much faster than separate INSERTs)
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES
('Charlie Park', 1, 95000, '2024-03-01'),
('Diana Lopez', 3, 78000, '2024-03-15'),
('Eve Johnson', 2, 88000, '2024-04-01');
#INSERT from a SELECT
sql
-- Copy rows from one table to another
INSERT INTO employee_archive (name, department_id, salary, hire_date)
SELECT name, department_id, salary, hire_date
FROM employees
WHERE hire_date < '2020-01-01';
-- This is powerful: you can transform and filter data as you insert
#Handling Conflicts
sql
-- INSERT OR IGNORE: skip if a unique constraint is violated
INSERT OR IGNORE INTO employees (id, name, department_id, salary)
VALUES (1, 'Alice Chen', 3, 85000);
-- If id=1 already exists, nothing happens (no error)
-- INSERT OR REPLACE: overwrite the existing row on conflict
INSERT OR REPLACE INTO employees (id, name, department_id, salary)
VALUES (1, 'Alice Chen Updated', 3, 90000);
-- If id=1 exists, the old row is deleted and this new row is inserted
Try it! Insert a few rows into the employees table and then SELECT to verify they were added.
Loading visualization...
#UPDATE — Modifying Existing Rows
#Basic UPDATE
sql
-- Give Alice a raise
UPDATE employees
SET salary = 95000
WHERE name = 'Alice Chen';
-- Update multiple columns at once
UPDATE employees
SET salary = 100000,
department_id = 1
WHERE name = 'Alice Chen';
#UPDATE with Calculations
sql
-- Give everyone in department 3 a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 3;
-- Set a minimum salary floor
UPDATE employees
SET salary = 50000
WHERE salary < 50000;
#UPDATE with Subqueries
sql
-- Set each employee's salary to their department's average
UPDATE employees
SET salary = (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = employees.department_id
);
-- Warning: this updates ALL employees. Be careful with updates that use subqueries.
What Do You Think?
What happens when you run: UPDATE employees SET salary = 0; (with no WHERE clause)?
Loading visualization...
#DELETE — Removing Rows
#Basic DELETE
sql
-- Delete a specific employee
DELETE FROM employees
WHERE id = 42;
-- Delete all employees in a department
DELETE FROM employees
WHERE department_id = 5;
-- Delete employees who haven't logged in for a year
DELETE FROM employees
WHERE last_login < DATE('now', '-1 year');
#DELETE vs. DROP vs. TRUNCATE
sql
-- DELETE: remove specific rows (can use WHERE, logged, can rollback)
DELETE FROM employees WHERE department_id = 5;
-- DELETE all rows (table structure remains)
DELETE FROM employees;
-- DROP: remove the entire table (structure + data, gone forever)
DROP TABLE employees;
-- TRUNCATE: remove all rows instantly (faster than DELETE, resets auto-increment)
-- Not available in SQLite, but common in PostgreSQL/MySQL
TRUNCATE TABLE employees;
| Command | Removes Rows | Removes Table | WHERE Clause | Can Rollback | Speed |
|---|---|---|---|---|---|
| DELETE | Yes | No | Yes | Yes | Slower (logs each row) |
| TRUNCATE | Yes | No | No | Depends on DB | Very fast |
| DROP | Yes | Yes | No | No | Instant |
What Do You Think?
What is the difference between DELETE FROM employees; and DROP TABLE employees;?
Loading visualization...
#Transactions — All or Nothing
#Basic Transaction
sql
-- Transfer $500 from account A to account B
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
-- Everything looks good? Make it permanent.
COMMIT;
#Rollback on Error
sql
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
-- Oops, account B doesn't exist! Undo everything.
ROLLBACK;
-- The $500 deduction from account A is reversed
#The ACID Properties
Transactions guarantee four properties:
- Atomicity: All operations succeed or all fail. No partial updates.
- Consistency: The database moves from one valid state to another. Constraints are enforced.
- Isolation: Concurrent transactions don't interfere with each other.
- Durability: Once committed, changes survive crashes and power failures.
See how different isolation levels let concurrent transactions interfere (or not) interactively:
Loading visualization...
#Practical Transaction Patterns
sql
-- Pattern: Safe bulk update with preview
BEGIN TRANSACTION;
-- Make changes
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 3;
-- Check the results
SELECT name, salary FROM employees WHERE department_id = 3;
-- Happy with the results? COMMIT
-- Not happy? ROLLBACK
COMMIT; -- or ROLLBACK;
sql
-- Pattern: Insert parent and child rows together
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total, status)
VALUES (42, 299.99, 'pending');
-- Use last_insert_rowid() to get the new order's ID
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (last_insert_rowid(), 101, 2, 149.99);
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (last_insert_rowid(), 102, 1, 0.01);
COMMIT;
Loading visualization...
#Putting It All Together
Here is a realistic sequence that combines INSERT, UPDATE, DELETE, and transactions:
sql
-- Onboard a new employee and update department headcount
BEGIN TRANSACTION;
-- 1. Add the new employee
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES ('Frank Wu', 2, 82000, '2024-06-01');
-- 2. Update the department's headcount
UPDATE departments
SET headcount = headcount + 1
WHERE id = 2;
-- 3. Remove the job posting (it's been filled)
DELETE FROM job_postings
WHERE department_id = 2 AND title = 'Software Engineer';
-- 4. Log the change
INSERT INTO audit_log (action, details, created_at)
VALUES ('HIRE', 'Frank Wu joined department 2', CURRENT_TIMESTAMP);
COMMIT;
If any of these four steps fails, ROLLBACK undoes all of them. The new employee is not added without the headcount being updated. The job posting is not deleted without the employee being added. Everything stays consistent.
Take a breath. You now know how to create, read, update, and delete data — the four fundamental operations (CRUD) that every application is built on. The hardest part is not the syntax; it is remembering to always include WHERE on UPDATE and DELETE. Make that a habit and you will avoid 90% of SQL disasters.
Quick Check1 / 5
What is the safest way to run an UPDATE on a production database?
What's next: Now that you can read and write, you'll learn to design the tables themselves with CREATE TABLE. Column types, constraints, primary keys, foreign keys — the schema decisions that determine whether your app stays clean or accumulates technical debt for years.