INSERT, UPDATE, DELETE

Reviewed & published by Brayan K

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.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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)

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.)

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.

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:

-- The table this lesson uses:
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- 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.

-- Read it back so you can see the new row is really there.
SELECT * FROM products ORDER BY id;

-- ✅ Expected 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
-- 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.

-- Uses the products table from the first example.
-- 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.

SELECT * FROM products ORDER BY id DESC LIMIT 3;

-- ✅ Expected result:
-- id | product_name | category | price | stock
-- 8 | Webcam HD | Electronics | 45 | 60
-- 7 | Desk Organizer | Home | 18.5 | 90
-- 6 | USB-C Cable | Electronics | 12.99 | 200

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.

-- Uses the products table from the first example.
-- 🎯 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 | 400

SELECT * FROM products WHERE id = 9;

2. 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:

-- Uses the products table from the first example.
-- 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.

-- Check the change landed on exactly one row.
SELECT id, product_name, price FROM products WHERE id IN (1, 3, 6) ORDER BY id;

-- ✅ Expected result:
-- id | product_name | price
-- 1 | Wireless Mouse | 24.99
-- 3 | Mechanical Keyboard | 69
-- 6 | USB-C Cable | 12.99

Before → After (only id 3 changes):

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:

-- Uses the products table from the first example.
-- 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.

SELECT id, product_name, price, stock FROM products WHERE id = 2;

-- ✅ Expected result:
-- id | product_name | price | stock
-- 2 | Coffee Mug | 8.75 | 350

Before → After (only id 2 changes):

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.

-- Uses the products table from the first example.
-- 🎯 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.

SELECT id, product_name, price FROM products WHERE id = 5;

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:

-- Uses the products table from the first example.
-- ⚠️ 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.
--
-- The whole demonstration is wrapped in BEGIN ... ROLLBACK so that running it
-- does not wreck the table the rest of this lesson uses. That is also the real
-- lesson: a transaction is the only thing standing between a missing WHERE and
-- a very bad afternoon.
BEGIN;

UPDATE products
SET price = 0.00;

-- A DELETE without WHERE is worse — it empties the whole table:
DELETE FROM products;   -- removes every row

SELECT COUNT(*) AS rows_remaining FROM products;

ROLLBACK;   -- put it all back; without this the damage would be permanent

-- Read every UPDATE and DELETE twice and make sure WHERE is there.

-- ✅ Expected result:
-- rows_remaining
-- 0

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.

-- Uses the products table from the first example.
-- 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.

SELECT id, product_name FROM products ORDER BY id;

-- ✅ Expected result:
-- id | product_name
-- 1 | Wireless Mouse
-- 2 | Coffee Mug
-- 3 | Mechanical Keyboard
-- 5 | Desk Lamp
-- 6 | USB-C Cable
-- 7 | Desk Organizer
-- 8 | Webcam HD

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.

-- Uses the products table from the first example — as it stands after the
-- DELETE two blocks up, so the Notebook has already gone for good.
-- 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 = 'Kitchen';   -- removes the Coffee Mug

-- Preview the damage before you commit. The Coffee Mug is gone from here:
SELECT id, product_name, category FROM products ORDER BY id;

-- Happy with it?  COMMIT;
-- Made a mistake?  ROLLBACK;   -- undoes the DELETE as if it never ran
ROLLBACK;

-- Read it back AFTER the rollback. The Coffee Mug is back, because nothing
-- inside a transaction is real until you COMMIT it.
SELECT id, product_name, category FROM products ORDER BY id;

-- ✅ Expected result:
-- id | product_name | category
-- 1 | Wireless Mouse | Electronics
-- 2 | Coffee Mug | Kitchen
-- 3 | Mechanical Keyboard | Electronics
-- 5 | Desk Lamp | Home
-- 6 | USB-C Cable | Electronics
-- 7 | Desk Organizer | Home
-- 8 | Webcam HD | Electronics

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-WHERE disaster: UPDATE products SET price = 0; or DELETE FROM products; changes/removes every row, not one. Always include a WHERE, and preview it with SELECT first. This is the costliest beginner mistake in SQL.
• "table products has 5 columns but 4 values were supplied": a column-less INSERT must 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 id that already exists. Primary keys must be unique — use a new id, or let the database auto-generate it.

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.

-- Uses the products table from the first example.
-- 🎯 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 Keyboard  86.90
--   6  USB-C Cable          14.289
--   (Coffee Mug, Notebook, Desk Lamp are NOT changed)

-- your query here

🎉 Lesson Complete

• ✅ INSERT INTO … VALUES adds rows — name your columns for safety
• ✅ UPDATE … SET … WHERE changes the rows that match the condition
• ✅ DELETE FROM … WHERE removes whole rows that match
• ✅ No WHERE = every row changes — preview with SELECT first
• ✅ Transactions (BEGIN / COMMIT / ROLLBACK) let you undo a mistake
• ✅ Next: Indexes & Performance — make your queries lightning fast

Practice quiz

Continue this course

• Previous: Subqueries
• Next: Indexes & Performance — Speed up queries dramatically by creating the right indexes
• Quick reference: SQL cheat sheet › Modifying Data