INSERT, UPDATE, DELETE.
Modify data. Don't destroy it.
SELECT reads data. INSERT, UPDATE, and DELETE modify it. Together they're CRUD — the four operations every application performs on every table. These are the queries that can destroy your data if you get them wrong. This lesson teaches the syntax, the transactions that protect you, and the mistakes that have wiped production databases. Every example shows what actually happens to the data.
Three commands modify data: INSERT adds rows, UPDATE changes values, DELETE removes rows. The WHERE clause in UPDATE and DELETE is critical — omitting it affects every row. Wrap related changes in a transaction (BEGIN; ... COMMIT;) so you can ROLLBACK if something goes wrong. For user data, prefer soft deletes (UPDATE deleted_at = now()) over hard deletes.
Before this lesson: complete Lesson 4. You need SELECT, WHERE, and the query fundamentals.
➕ INSERT — add rows
Always specify columns. Always. Auto-increment IDs and defaults do the rest.
INSERT adds new rows to a table. The syntax is INSERT INTO table (columns) VALUES (values). Columns and values must match in order. Always specify the column list — it's safer, faster, and self-documenting.
-- Insert one row. id and created_at have defaults.
INSERT INTO users (name, email, country, age)
VALUES ('Diana Park', 'diana@example.com', 'US', 29);
-- Verify with a SELECT
SELECT * FROM users WHERE email = 'diana@example.com';
| id | name | country | age | created_at | |
|---|---|---|---|---|---|
| 9 | Diana Park | diana@example.com | US | 29 | 2026-10-26 |
Insert multiple rows in one statement — much faster than one INSERT per row:
-- Bulk insert. One statement, three rows.
INSERT INTO users (name, email, country, age)
VALUES
('Emma Wilson', 'emma@example.com', 'UK', 27),
('Jake Torres', 'jake@example.com', 'US', 32),
('Yuki Tanaka', 'yuki@example.com', 'JP', 30);
-- Verify
SELECT id, name, country FROM users
WHERE email IN ('emma@example.com', 'jake@example.com', 'yuki@example.com');
| id | name | country |
|---|---|---|
| 10 | Emma Wilson | UK |
| 11 | Jake Torres | US |
| 12 | Yuki Tanaka | JP |
Always specify columns
INSERT INTO users VALUES (...) without column names works, but breaks when the schema changes and depends on column order. Explicit column lists are self-documenting.
Auto-increment IDs
Usually id SERIAL PRIMARY KEY or id INT AUTO_INCREMENT. The database assigns the next value automatically. Don't insert the ID yourself.
DEFAULT values
Columns can have defaults: created_at TIMESTAMP DEFAULT NOW(). Omit them from INSERT and the default applies. Useful for timestamps and booleans.
Bulk insert
VALUES (row1), (row2), (row3) inserts many rows in one statement. 10-100x faster than separate INSERTs. Use for imports and seed data.
Bulk insert is dramatically faster than looping INSERTs. One INSERT with 1,000 rows is roughly 100x faster than 1,000 separate INSERTs — because each statement in a loop has overhead (network round-trip, transaction commit, index updates). If you're inserting more than a few rows, use a single bulk INSERT. This is why import scripts always use bulk insert.
INSERT without columns breaks when the schema changes. INSERT INTO users VALUES ('Alice', 'alice@x.com', 'US') works today. Tomorrow someone adds a column to the middle of the table — your INSERT now fails or, worse, silently inserts values into the wrong columns. Always list the columns explicitly. It costs ten seconds and prevents a class of bugs that are hard to debug.
✏️ UPDATE — modify existing rows
The WHERE clause is not optional. Without it, every row changes.
UPDATE changes existing rows. The syntax is UPDATE table SET column = value WHERE condition. The WHERE clause determines which rows get updated — without it, every row in the table gets updated. This is one of the most common SQL disasters.
-- Update one specific user's email UPDATE users SET email = 'alice.new@example.com' WHERE id = 1; -- Verify — only Alice changed SELECT id, name, email FROM users WHERE id = 1;
Before
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice@example.com |
After
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice.new@example.com |
Update multiple columns in one statement. Multiple rows match the WHERE, so multiple rows update:
-- Update multiple columns for multiple rows -- (every US user's country becomes "USA") UPDATE users SET country = 'USA', updated_at = NOW() WHERE country = 'US'; -- Check the affected rows SELECT id, name, country FROM users WHERE country = 'USA';
After — all US rows now "USA"
| id | name | country |
|---|---|---|
| 1 | Alice Chen | USA |
| 4 | Marcus Lee | USA |
| 9 | Diana Park | USA |
| 11 | Jake Torres | USA |
WHERE is required
UPDATE users SET email = 'x' without WHERE sets every row's email to 'x'. There's no undo (unless you're in a transaction). Always write WHERE first.
Multiple SET columns
SET a = 1, b = 2 updates both columns atomically. Comma-separated. Usually paired with an updated_at column that gets a fresh timestamp.
Row count feedback
Most databases report how many rows were affected. In production code, check this number — if you expected 1 row and 5,000 were updated, something's wrong. Wrap in a transaction for safety.
UPDATE with JOIN
Some databases support UPDATE ... FROM (PostgreSQL) or UPDATE ... JOIN (MySQL). Updates based on a related table's data. Advanced but useful.
The #1 way developers destroy production data: running UPDATE users SET status = 'inactive' without a WHERE clause. Every row in the table becomes inactive — all users, all accounts, all at once. The query returns "50,000 rows updated" and the damage is done. Prevent this: (1) always write WHERE first, then the SET, (2) wrap in a transaction, (3) check the affected row count before committing.
🗑️ DELETE — remove rows
Same warning as UPDATE. Without WHERE, everything goes.
-- Delete one specific user DELETE FROM users WHERE id = 12; -- Delete inactive users older than 30 days DELETE FROM users WHERE active = false AND last_login < NOW() - INTERVAL '30 days'; -- ⚠️ DANGER: no WHERE clause deletes EVERYTHING -- DELETE FROM users; -- Returns "12 rows deleted". There is no undo.
Before — 12 rows
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice.new@example.com |
| 2 | Bob Smith | bob@example.com |
| 3 | Priya Patel | priya@example.com |
| ... | ... | ... |
| 12 | Yuki Tanaka | yuki@example.com |
After — 11 rows (Yuki removed)
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice.new@example.com |
| 2 | Bob Smith | bob@example.com |
| 3 | Priya Patel | priya@example.com |
| ... | ... | ... |
DELETE removes specific rows
With WHERE: only matching rows are removed. Row by row, one at a time — slower than TRUNCATE but safer and more flexible.
DELETE without WHERE
Deletes every row but leaves the table structure intact. Equivalent to TRUNCATE but slower and transactional. Almost always a mistake.
TRUNCATE
Empties the table in one metadata operation. Much faster than DELETE. Cannot be rolled back in most databases. Resets auto-increment counters. Use for "wipe the table" operations.
No undo
Once committed, DELETE is permanent. Transactions are the only protection — BEGIN; DELETE ...; -- check --; ROLLBACK; if wrong. For user data, prefer soft delete.
DELETE FROM users (no WHERE) is the most common self-inflicted production disaster. It deletes every row in the table — no confirmation, no undo, no "are you sure?" prompt. The rows are gone the moment the query commits. If you're going to run a DELETE, wrap it in a transaction first: BEGIN; DELETE ...; -- check row count --; COMMIT; or ROLLBACK;. Or better, use soft delete for user-facing data.
🔒 Transactions — all or nothing
The safety net for every data modification. Group related changes so they all succeed or all fail.
A transaction groups multiple SQL statements into a single atomic unit. Either all statements succeed (COMMIT) or all are rolled back (ROLLBACK). This is what prevents partially-applied changes — like money leaving one account but not arriving in another.
-- Transfer $100 from Alice to Bob -- Both changes must succeed or neither should BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Alice UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Bob -- Before committing, verify both updates worked SELECT id, name, balance FROM accounts WHERE id IN (1, 2); -- If balances look correct: COMMIT; -- If something is wrong: -- ROLLBACK; -- What a transaction protects against: -- If the second UPDATE fails (constraint violation, -- network error, crash), ROLLBACK undoes the first UPDATE. -- Without the transaction, Alice is out $100 and Bob -- never received it — money vanishes.
Before transaction
| id | name | balance |
|---|---|---|
| 1 | Alice | 500.00 |
| 2 | Bob | 200.00 |
After COMMIT
| id | name | balance |
|---|---|---|
| 1 | Alice | 400.00 |
| 2 | Bob | 300.00 |
BEGIN / COMMIT / ROLLBACK
BEGIN starts a transaction. COMMIT saves all changes. ROLLBACK undoes all changes since BEGIN. The three commands are the entire transaction API.
Atomicity
All statements succeed or none do. If the second UPDATE fails, the first is rolled back automatically (if the transaction is properly structured). This is why bank transfers work.
Isolation
Other connections can't see your uncommitted changes. They see the data as it was before your transaction started, until you commit. Prevents "dirty reads."
Consistency
After commit, the database is in a valid state. Constraints are enforced, foreign keys are respected. If the transaction would break a constraint, it's rolled back.
Wrap every destructive operation in a transaction. Before any UPDATE, DELETE, or INSERT that affects existing data, run BEGIN. After the operation, check the row count — if it matches expectations, COMMIT. If not, ROLLBACK. This costs nothing (transactions are fast) and has saved every developer from a production disaster at least once. It's the single most important habit for safe SQL.
🔄 UPSERT — insert or update
When you don't know if the row exists. Avoids the SELECT-then-INSERT race condition.
-- PostgreSQL / SQLite: INSERT ... ON CONFLICT
INSERT INTO users (email, name, country)
VALUES ('alice.new@example.com', 'Alice Chen', 'USA')
ON CONFLICT (email) DO UPDATE SET
name = EXCLUDED.name,
country = EXCLUDED.country,
updated_at = NOW();
-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
-- INSERT INTO users (email, name, country)
-- VALUES ('alice.new@example.com', 'Alice Chen', 'USA')
-- ON DUPLICATE KEY UPDATE
-- name = VALUES(name),
-- country = VALUES(country);
-- Behavior:
-- If email doesn't exist: inserts new row.
-- If email exists: updates that row with new name + country.
-- One atomic statement. No race condition.
Result: updated existing row
| id | name | country | |
|---|---|---|---|
| 1 | Alice Chen | alice.new@example.com | USA |
What it does
Tries to INSERT. If the row violates a unique constraint (usually on id or email), runs an UPDATE on the conflicting row instead. One atomic statement.
Why not SELECT then INSERT?
Between your SELECT (row not found) and INSERT, another connection could insert the same row. You get a duplicate key error — or worse, two rows. UPSERT avoids this race entirely.
Database-specific syntax
PostgreSQL/SQLite: ON CONFLICT. MySQL: ON DUPLICATE KEY UPDATE. SQL Server: MERGE. Oracle: MERGE. Concept identical, syntax varies.
Use cases
Syncing from external sources (import data that may already exist). Saving user preferences (set-if-missing-or-update). Idempotent API endpoints (same request twice has no extra effect).
UPSERT is the safe way to sync data. If your application syncs data from Stripe, GitHub, or a webhook, an UPSERT is what you want — the same event delivered twice should update the existing row, not crash on the unique constraint or create a duplicate. Every production sync system uses UPSERT. If you're writing SELECT-then-INSERT logic, you're building a race condition.
🌫️ Soft delete — the safer pattern
For user data, don't DELETE. Mark as deleted. Recoverable, auditable, joinable.
-- Instead of DELETE FROM users WHERE id = 12; -- mark as deleted: UPDATE users SET deleted_at = NOW() WHERE id = 12; -- Active users query — filters out soft-deleted rows SELECT id, name, email FROM users WHERE deleted_at IS NULL; -- All users (including deleted) — for admin/audit SELECT id, name, email, deleted_at FROM users; -- Yuki Tanaka appears with a timestamp -- Restore a soft-deleted user UPDATE users SET deleted_at = NULL WHERE id = 12; -- Per-user email uniqueness with soft delete: -- Use a partial unique index so deleted users -- don't block re-signup with the same email. -- CREATE UNIQUE INDEX users_email_active -- ON users (email) -- WHERE deleted_at IS NULL;
Active users (WHERE deleted_at IS NULL)
| id | name | |
|---|---|---|
| 1 | Alice Chen | alice.new@example.com |
| 2 | Bob Smith | bob@example.com |
| 3 | Priya Patel | priya@example.com |
All users (including deleted)
| id | name | deleted_at |
|---|---|---|
| 1 | Alice Chen | NULL |
| 2 | Bob Smith | NULL |
| 3 | Priya Patel | NULL |
| 12 | Yuki Tanaka | 2026-10-26 14:32:15 |
How it works
Add a deleted_at TIMESTAMP column. Instead of DELETE, run UPDATE SET deleted_at = NOW(). Every query filters WHERE deleted_at IS NULL.
Why use it
Accidental deletes are recoverable (just set deleted_at = NULL). Audits and legal hold work. Foreign keys don't break. Users can "restore" their account.
The cost
Every query must filter by deleted_at. Easy to forget — use a view or an ORM scope. Storage grows (deleted rows still exist). Add a cleanup job that hard-deletes older than N days.
When to hard delete
GDPR "right to be forgotten" requests. Logs and sessions (disposable by nature). Test data. Bulk cleanup. For primary user data, soft delete is usually the right choice.
Most production apps use soft delete for user data and hard delete for ephemeral data. When you click "delete my account" on most sites, the row doesn't disappear — it gets marked as deleted and stays in the database for 30-90 days before a cleanup job removes it. This is what allows "undo delete" features, GDPR compliance workflows, and recovery from accidental clicks. Hard delete is for logs, sessions, and truly disposable data.
🏆 Challenge
Three tasks. Each exercises a different modification pattern.
Task 1 — Predict the damage
For each of these buggy statements, predict what happens to the table:
1. UPDATE users SET country = 'US'; — no WHERE
2. DELETE FROM users WHERE email = NULL; — wrong NULL syntax
3. INSERT INTO users VALUES ('Alice'); — no column list
4. UPDATE users SET email = 'x@y.com' WHERE id = 1 OR id = 2; — two conditions, one update
5. DELETE FROM users WHERE id = 1; DELETE FROM orders WHERE user_id = 1; — two separate statements, no transaction
Which are safe? Which are dangerous? Which fail with an error? For the dangerous ones, what's the fix?
Task 2 — Write safe modifications
Using a users table (id, name, email, country, active, deleted_at, created_at):
1. Insert 5 new users with a bulk INSERT
2. Update all inactive users to have deleted_at = NOW()
3. Delete all users created before 2020 (hard delete, wrapped in a transaction)
4. Soft-delete a user by id, then restore them
5. Insert a user, and if email exists, update their name (UPSERT)
6. Run a safe UPDATE that you can roll back if the row count is wrong
For each query, write the transaction wrapper and describe how you'd verify the result before committing.
Task 3 — Design a safe delete flow
You're building a "delete my account" feature for a SaaS product. Design the flow:
1. Should it hard delete or soft delete? Why?
2. What columns do you need to add to support it?
3. How do queries on the rest of the app know to exclude deleted rows?
4. How would you handle foreign keys (orders referencing the deleted user)?
5. When would you actually hard delete the row?
6. What does "undo delete" look like?
Then write the SQL for the delete and the undo. Then write the SQL for the cleanup job that hard-deletes users soft-deleted more than 30 days ago.
⚠️ Common mistakes
The data-modification bugs that destroy databases.
UPDATE users SET status = 'inactive' updates every row. Fix: always write WHERE first, then the SET. Or wrap in a transaction and check the row count.
DELETE FROM users removes every row. No undo. Fix: same as above. Write WHERE first, or use soft delete for user data.
Two UPDATEs where the second fails leaves the database inconsistent. Fix: BEGIN; UPDATE; UPDATE; COMMIT; — or ROLLBACK if anything's wrong.
INSERT INTO users VALUES (...) breaks when schema changes. Fix: always list columns explicitly — INSERT INTO users (name, email) VALUES (...).
Two requests check "does this email exist?" at the same time, both see "no", both INSERT. Result: duplicate. Fix: use UPSERT with a unique constraint.
Deleting a user with orders fails (or cascades and removes the orders). Fix: decide the behavior — cascade, restrict, or soft delete. Set it explicitly in the schema.
You soft-delete a user, but the search page still shows them because the query didn't filter WHERE deleted_at IS NULL. Fix: use views or ORM scopes that filter by default.
Inserting 10,000 rows in a loop with 10,000 separate INSERT statements is 100x slower than one bulk INSERT. Fix: use INSERT INTO t VALUES (r1), (r2), ... for imports.
➡️ What's next
You can modify data. Next: making queries fast.
❓ Frequently asked questions
How do I insert a row in SQL?
Use INSERT INTO table (columns) VALUES (values). Example: INSERT INTO users (name, email, country) VALUES ('Alice', 'alice@example.com', 'US'). The columns and values must be in the same order. Always specify the column list.
How do I update rows in SQL?
Use UPDATE table SET column = value WHERE condition. Example: UPDATE users SET email = 'new@example.com' WHERE id = 1. The WHERE clause is critical — without it, every row in the table gets updated.
How do I delete rows in SQL?
Use DELETE FROM table WHERE condition. Example: DELETE FROM users WHERE id = 1. The WHERE clause is critical — DELETE FROM users; with no WHERE deletes every row in the table.
What is the difference between DELETE and TRUNCATE?
DELETE removes specific rows (or all rows if no WHERE) and is transactional. TRUNCATE removes all rows quickly, resets auto-increment counters, and cannot be rolled back in most databases. DELETE is slower; TRUNCATE is faster.
What is a SQL transaction?
A transaction groups multiple SQL statements into a single atomic unit. Either all statements succeed (COMMIT) or all are rolled back (ROLLBACK). Used when you need to modify related rows together. Syntax: BEGIN; ... COMMIT; (or ROLLBACK on error).
What is an UPSERT in SQL?
UPSERT means insert-if-not-exists or update-if-exists. PostgreSQL/SQLite: INSERT ... ON CONFLICT. MySQL: INSERT ... ON DUPLICATE KEY UPDATE. SQL Server: MERGE. Used to safely insert data that might already exist.
What is a soft delete?
A soft delete marks a row as deleted without actually removing it, using a column like deleted_at or is_deleted. Queries filter WHERE deleted_at IS NULL. Used for user-facing data so you can restore, audit, or join deleted rows later.
How do I undo a DELETE in SQL?
You can undo a DELETE if (1) you wrapped it in a transaction and haven't committed yet (use ROLLBACK), or (2) you have a backup. Once committed, there's no built-in undo. For production data, use soft deletes instead of hard deletes.