Lesson 10 • Intermediate Track
INSERT, UPDATE, DELETE
So far you've only read data with SELECT. By the end of this lesson you'll be able to change it — adding new rows with INSERT, editing rows with UPDATE, and removing rows with DELETE — and you'll know the one safety rule that separates a confident developer from a costly accident.
What You'll Learn
- ✓Add a row with INSERT INTO ... VALUES
- ✓Insert safely with an explicit column list
- ✓Change rows with UPDATE ... SET ... WHERE
- ✓Remove rows with DELETE FROM ... WHERE
- ✓Why a missing WHERE rewrites EVERY row
- ✓Use transactions to undo a mistake (ROLLBACK)
WHERE clause — it's the single most important part of every command in this lesson. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the table before and after so you can check yourself. Our Sample Table: products
Every command in this lesson modifies this same products table. This is its starting state — picture it changing as each example runs. (Each example assumes you start from this fresh table.)
Result:
| id | product_name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Coffee Mug | Kitchen | 9.5 | 300 |
| 3 | Mechanical Keyboard | Electronics | 79 | 45 |
| 4 | Notebook | Stationery | 3.25 | 500 |
| 5 | Desk Lamp | Home | 32 | 80 |
| 6 | USB-C Cable | Electronics | 12.99 | 200 |
1. INSERT — Adding New Rows
INSERT INTO adds a brand-new row to a table. You give it the values for the new row, and it appends it to the bottom. Unlike SELECT (which only reads), INSERT actually changes what's stored.
🗄️ Real-world analogy
INSERT is like filling out a new form and dropping it into a filing cabinet. You decide which fields to fill in (product_name, price, …) and what to write in each one; the cabinet just gains one more sheet.
There are two shapes. The short one leaves out the column names, so your VALUES must cover every column in the table's exact order:
INSERT INTO … VALUES
Add a row by supplying every column value in order.
-- INSERT adds a brand-new row to the table
-- Here the column list is left out, so you must supply a value
-- for EVERY column, in the table's exact order (id, product_name, category, price, stock):
INSERT INTO products
VALUES (7, 'Desk Organizer', 'Home', 18.50, 90);
-- After this runs, the products table has 7 rows instead of 6.
-- The new row's id is 7 because that's the value you gave it.Result — the table now has 7 rows:
| id | product_name | category | price | stock |
|---|---|---|---|---|
| … | … | … | … | … |
| 6 | USB-C Cable | Electronics | 12.99 | 200 |
| 7 | Desk Organizer | Home | 18.5 | 90 |
The safer, more professional shape lists the columns first. Now the values line up with your list rather than the table's hidden ordering — so the statement keeps working even if the table changes later.
INSERT with a column list
Name the columns, then supply matching values.
-- The SAFE way: list the columns, then the matching values
INSERT INTO products (id, product_name, category, price, stock)
VALUES (8, 'Webcam HD', 'Electronics', 45.00, 60);
-- Now the order of VALUES is tied to YOUR column list, not the
-- table's. If someone adds a column later, this still works —
-- which is why naming columns is the professional habit.Result — row 8 added:
| id | product_name | category | price | stock |
|---|---|---|---|---|
| … | … | … | … | … |
| 8 | Webcam HD | Electronics | 45 | 60 |
INSERT INTO products (id, product_name, …). The column-less form silently breaks the day someone adds or reorders a column, and the bug can be hard to spot.Your Turn: insert a product
Fill in the four missing values to add a Gel Pen. They must line up with the column list, in order. The expected result is in the comments so you can check yourself.
🎯 Your Turn: INSERT a row
Replace the ___ blanks with the four remaining values.
-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: add a new product called 'Gel Pen' in the 'Stationery'
-- category, priced 1.80, with 400 in stock and id 9.
INSERT INTO products (id, product_name, category, price, stock)
VALUES (9, ___, ___, ___, ___); -- 👉 fill the four remaining values in column order
-- ✅ Expected: the table gains a 7th id (id 9):
-- 9 | Gel Pen | Stationery | 1.80 | 4002. UPDATE — Changing Existing Rows
UPDATE edits rows that are already in the table. It has three parts: the table to change, a SET clause naming the column(s) and their new value(s), and a WHERE clause that decides which rows are affected. The WHERE is the part you must never forget.
This first example lowers the price of a single product — the one whose id is 3:
UPDATE one row
Change the price of the product with id = 3.
-- UPDATE changes values in rows that match the WHERE clause.
-- This drops the price of ONE product — the one with id = 3.
UPDATE products
SET price = 69.00
WHERE id = 3;
-- SET picks the column and its new value; WHERE picks the rows.
-- Only id 3 (the Mechanical Keyboard) is touched — nothing else.Before → After (only id 3 changes):
Result — before:
| id | product_name | price |
|---|---|---|
| 3 | Mechanical Keyboard | 79 |
Result — after:
| id | product_name | price |
|---|---|---|
| 3 | Mechanical Keyboard | 69 |
You can change several columns at once by separating them with commas in the SET clause. Here both the price and the stock of id 2 change in a single statement:
UPDATE several columns
Reprice and restock id = 2 in one statement.
-- You can change several columns in one statement,
-- separated by commas. This restocks AND reprices id 2.
UPDATE products
SET price = 8.75,
stock = 350
WHERE id = 2;
-- Both columns on the Coffee Mug row change together.
-- The other five rows are untouched.Before → After (only id 2 changes):
Result — before:
| id | product_name | price | stock |
|---|---|---|---|
| 2 | Coffee Mug | 9.5 | 300 |
Result — after:
| id | product_name | price | stock |
|---|---|---|---|
| 2 | Coffee Mug | 8.75 | 350 |
Your Turn: update one price
Fill in the new price and the id. Notice how the WHERE id = … line is what stops this from repricing the whole table — that's the habit to build.
🎯 Your Turn: UPDATE with WHERE
Set the Desk Lamp (id 5) price to 35.00 — and nothing else.
-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: raise the Desk Lamp's price to 35.00.
-- The Desk Lamp is id 5. The WHERE clause is what keeps you safe!
UPDATE products
SET price = ___ -- 👉 the new price
WHERE id = ___; -- 👉 WITHOUT this line you'd reprice EVERY product
-- ✅ Expected: only id 5 changes — Desk Lamp's price becomes 35.00.3. The One Rule: Never UPDATE or DELETE Without WHERE
This is the most important paragraph in the lesson. UPDATE and DELETE apply to every row that matches WHERE. If you leave WHERE off, every row matches — so the change hits the entire table at once. There is no "are you sure?" pop-up.
Read the example below carefully — it is what a mistake looks like, so you can recognise and avoid it:
⚠️ What a missing WHERE does
Study this — it overwrites or deletes EVERY row. Don't run it on real data.
-- ⚠️ DANGER — there is no WHERE clause here.
-- This does NOT update one row. It overwrites the price of
-- EVERY row in the table to 0.00:
UPDATE products
SET price = 0.00;
-- All 6 products now cost 0.00. A DELETE without WHERE is worse —
-- it empties the whole table:
DELETE FROM products; -- removes every row
-- Read every UPDATE and DELETE twice and make sure WHERE is there.SELECT — e.g. SELECT * FROM products WHERE id = 3; — to see exactly which rows you're about to change, then swap SELECT * for UPDATE … SET … or DELETE.4. DELETE — Removing Rows
DELETE FROM removes whole rows that match the WHERE clause. It deletes entire rows, not single values — if you only want to clear one field, use UPDATE to set it to a new value instead.
Think of DELETE … WHERE id = 4 as pulling exactly one sheet out of the filing cabinet and shredding it. DELETE FROM products; with no WHERE shreds the whole drawer.
DELETE one row
Remove only the Notebook (id = 4).
-- DELETE removes whole rows that match the WHERE clause.
-- This removes ONLY the Notebook (id 4):
DELETE FROM products
WHERE id = 4;
-- After this runs the table has 5 rows — id 4 is gone.
-- DELETE removes entire rows; to blank one value, use UPDATE instead.Result — after — id 4 is gone, 5 rows remain:
| id | product_name | category | price | stock |
|---|---|---|---|---|
| 3 | Mechanical Keyboard | Electronics | 79 | 45 |
| 5 | Desk Lamp | Home | 32 | 80 |
| 6 | USB-C Cable | Electronics | 12.99 | 200 |
5. Transactions — Your Undo Button
A transaction wraps one or more changes so they don't become permanent until you say so. After BEGIN TRANSACTION, your INSERT/UPDATE/DELETE statements are only pending. COMMIT saves them for good; ROLLBACK throws them all away as if they never happened. It's the closest thing SQL has to an undo button — and your safety net for risky changes.
BEGIN / ROLLBACK
Make a change, preview it, then undo it with ROLLBACK.
-- A transaction is a safety net: changes are pending until you
-- COMMIT (save) them, and ROLLBACK throws them all away.
BEGIN TRANSACTION;
DELETE FROM products
WHERE category = 'Stationery'; -- removes the Notebook
-- Preview the damage before you commit:
SELECT * FROM products;
-- Happy with it? COMMIT;
-- Made a mistake? ROLLBACK; -- undoes the DELETE as if it never ran
ROLLBACK;Because the example ends in ROLLBACK, the table is left exactly as it started — all 6 rows still there. Swap ROLLBACK for COMMIT to make the deletion stick.
Common Errors (and the fix)
- The missing-
WHEREdisaster:UPDATE products SET price = 0;orDELETE FROM products;changes/removes every row, not one. Always include aWHERE, and preview it withSELECTfirst. This is the costliest beginner mistake in SQL. - "table products has 5 columns but 4 values were supplied": a column-less
INSERTmust give a value for every column. Either supply all of them, or list the columns you are filling:INSERT INTO products (product_name, price) VALUES (…). - Type mismatch: putting text where a number belongs —
SET price = 'cheap'— fails. Numbers have no quotes (price = 18.50); text values do (category = 'Home'). - "NOT NULL constraint failed": you left out a column the table requires. Provide a value for every required column (or give the column a default).
- "UNIQUE constraint failed: products.id": you inserted an
idthat already exists. Primary keys must be unique — use a newid, or let the database auto-generate it.
📘 Quick Reference
| Syntax | Purpose |
|---|---|
| INSERT INTO t VALUES (...) | Add a row (a value for every column, in order) |
| INSERT INTO t (cols) VALUES (...) | Add a row, naming the columns (preferred) |
| UPDATE t SET col = val WHERE ... | Change matching rows |
| UPDATE t SET a = x, b = y WHERE ... | Change several columns at once |
| DELETE FROM t WHERE ... | Remove matching rows |
| ⚠️ no WHERE | Affects EVERY row — almost never what you want |
| BEGIN / COMMIT / ROLLBACK | Save or undo a batch of changes |
Frequently Asked Questions
Q: What if I run an UPDATE or DELETE without a WHERE by accident?
Every row is affected. If you were inside a transaction (BEGIN TRANSACTION) you can ROLLBACK to undo it. If you'd already committed — or never started a transaction — you'll need a backup to recover. That's exactly why you preview with SELECT first.
Q: Do I have to list every column in an INSERT?
Only if you leave the column list out — then you must supply all of them in order. If you name the columns, you can fill just those (others get their default value or NULL). Naming columns is the safer habit.
Q: What's the difference between UPDATE and DELETE?
UPDATE keeps the row but changes a value inside it; DELETE removes the entire row. To "clear" one field, UPDATE it to NULL or a new value — don't DELETE the whole row.
Q: Does INSERT/UPDATE/DELETE change the data permanently?
Yes — these write to the table, unlike SELECT. Outside a transaction the change is immediate and permanent. Inside one, it's pending until you COMMIT, and reversible with ROLLBACK.
Mini-Challenge: A 10% Electronics Price Rise
Put it together — a brief, a blank canvas, and the expected before/after in the comments. The trick is the WHERE: it must limit the change to Electronics only. Write it, then copy it into a playground to confirm.
🎯 Mini-Challenge
UPDATE every Electronics product's price by +10% using WHERE category = 'Electronics'.
-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (UPDATE / SET / WHERE and maths):
-- Give every Electronics product a 10% price rise — and nothing else.
-- Remember: the WHERE clause is what limits the change to Electronics.
--
-- Starting prices (Electronics rows):
-- 1 Wireless Mouse 24.99
-- 3 Mechanical Keyboard 79.00
-- 6 USB-C Cable 12.99
--
-- ✅ Expected after your query (price * 1.10):
-- 1 Wireless Mouse 27.489
-- 3 Mechanical Keyboar
...🎉 Lesson Complete
- ✅
INSERT INTO … VALUESadds rows — name your columns for safety - ✅
UPDATE … SET … WHEREchanges the rows that match the condition - ✅
DELETE FROM … WHEREremoves whole rows that match - ✅ No
WHERE= every row changes — preview withSELECTfirst - ✅ Transactions (
BEGIN/COMMIT/ROLLBACK) let you undo a mistake - ✅ Next: Indexes & Performance — make your queries lightning fast
Sign up for free to track which lessons you've completed and get learning reminders.