Chapter 5 — Modifying Data
The first four chapters focused on reading data with SELECT. Now it's time to write.
This chapter covers the three DML statements that change the content of your tables:
INSERT to add rows, UPDATE to change existing rows, and
DELETE to remove them. We also look at TRUNCATE — a faster
way to empty a table entirely — and when to use each.
DML recap. Data Manipulation Language statements change row contents
without altering the table structure. They are transactional — you can wrap them in
BEGIN / COMMIT / ROLLBACK to undo mistakes
(covered in Chapter 10). For now we'll run them directly and see the effect immediately.
1. INSERT — Adding Rows
INSERT adds one or more new rows to a table. There are two forms: explicit column
names (recommended) and positional (fragile — avoid it).
Single-row INSERT with column names
INSERT INTO authors (first_name, last_name, nationality)
VALUES ('Matt', 'Haig', 'British');
-- Insert one author
INSERT INTO authors (first_name, last_name, nationality)
VALUES ('Matt', 'Haig', 'British');
Query OK, 1 row affected (0.01 sec)
-- MySQL auto-assigned author_id. Check it:
SELECT LAST_INSERT_ID(); -- returns the AUTO_INCREMENT value just generated
Always name your columns. Omitting the column list and relying on
position (INSERT INTO authors VALUES (1, 'Matt', 'Haig', 'British'))
breaks silently when someone adds, removes, or reorders a column on the table.
Named columns make the INSERT self-documenting and resilient to schema changes.
Omitting optional columns
You don't have to include every column. Columns with DEFAULT values or
that allow NULL can be left out entirely:
-- nationality is nullable — we can omit it
INSERT INTO authors (first_name, last_name)
VALUES ('Ursula', 'Le Guin');
-- nationality will be NULL for this row
-- stock has DEFAULT 0, so we can omit it when inserting a book
INSERT INTO books (author_id, title, price, published_year)
VALUES (3, 'The Left Hand of Darkness', 8.49, 1969);
-- stock defaults to 0 automatically
Multi-row INSERT
You can insert many rows in a single statement by listing multiple value groups.
This is much faster than running one INSERT per row — MySQL processes the batch in a
single operation:
INSERT INTO authors (first_name, last_name, nationality)
VALUES
('Frank', 'Herbert', 'American'),
('Yuval', 'Harari', 'Israeli'),
('Agatha', 'Christie', 'British'),
('George', 'Orwell', 'British');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
INSERT … SELECT
You can insert the results of a SELECT directly into a table. Useful for copying
rows between tables, archiving data, or seeding test tables:
-- Copy all British authors into an audit table
INSERT INTO british_authors (first_name, last_name)
SELECT first_name, last_name
FROM authors
WHERE nationality = 'British';
Seeding the bookshop — inserting our full dataset
Let's populate all four tables with enough data to make the rest of the course
meaningful. Run these in order — foreign key constraints require parent rows to exist
first:
-- 1. Authors (no dependencies)
INSERT INTO authors (first_name, last_name, nationality)
VALUES
('Matt', 'Haig', 'British'),
('Frank', 'Herbert', 'American'),
('Yuval', 'Harari', 'Israeli'),
('Agatha', 'Christie', 'British'),
('Ursula', 'Le Guin', 'American');
-- 2. Books (depend on authors)
INSERT INTO books (author_id, title, genre, price, stock, published_year)
VALUES
(1, 'The Midnight Library', 'Fiction', 8.99, 42, 2020),
(1, 'Reasons to Stay Alive', 'Non-fiction',7.99, 18, 2015),
(2, 'Dune', 'Sci-fi', 9.99, 27, 1965),
(3, 'Sapiens', 'Non-fiction',10.99, 31, 2011),
(4, 'And Then There Were None', 'Mystery', 6.99, 55, 1939),
(5, 'The Left Hand of Darkness', 'Sci-fi', 8.49, 9, 1969);
-- 3. Customers (no dependencies)
INSERT INTO customers (first_name, last_name, email, joined_date)
VALUES
('Alice', 'Nguyen', 'alice@example.com', '2022-03-10'),
('Ben', 'Okafor', 'ben@example.com', '2023-07-22'),
('Chloe', 'Martinez', 'chloe@example.com', '2021-11-05'),
('David', 'Singh', 'david@example.com', '2024-01-18');
-- 4. Orders (depend on customers and books)
INSERT INTO orders (customer_id, book_id, quantity, total_price, order_date)
VALUES
(1, 1, 2, 17.98, '2024-02-14 10:30:00'),
(1, 4, 1, 10.99, '2024-02-14 10:30:00'),
(2, 3, 1, 9.99, '2024-03-05 14:15:00'),
(3, 5, 3, 20.97, '2024-03-20 09:00:00'),
(4, 2, 1, 7.99, '2024-04-10 16:45:00');
With data in all four tables, every SELECT from here on will produce real results to
verify against.
2. UPDATE — Changing Existing Rows
UPDATE changes the values of one or more columns in rows that match a condition.
It does not add or remove rows — only modifies them.
UPDATE books
SET price = 11.99, stock = 35
WHERE book_id = 4;
-- Correct a price error on Sapiens
UPDATE books
SET price = 11.99
WHERE book_id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Before
| book_id | title | price |
|---|
| 4 | Sapiens | 10.99 |
After
| book_id | title | price |
|---|
| 4 | Sapiens | 11.99 |
Updating multiple columns at once
-- Update both price and stock in one statement
UPDATE books
SET price = 9.49,
stock = 50
WHERE book_id = 3; -- Dune
Updating with expressions
SET can use the column's current value in the expression — useful for increments,
percentage changes, or clearing a field relative to what's there now:
-- Apply a 10% price increase to all Fiction books
UPDATE books
SET price = ROUND(price * 1.10, 2)
WHERE genre = 'Fiction';
-- Restock all books that have run low
UPDATE books
SET stock = stock + 20
WHERE stock < 10;
-- Clear a nullable field (set it to NULL)
UPDATE authors
SET nationality = NULL
WHERE author_id = 5;
UPDATE with a subquery
-- Give a 5% discount to books by British authors
UPDATE books
SET price = ROUND(price * 0.95, 2)
WHERE author_id IN (
SELECT author_id
FROM authors
WHERE nationality = 'British'
);
UPDATE without WHERE updates every row in the table.
UPDATE books SET price = 0 will zero out all prices — there is no undo
unless you are inside a transaction. Always write the WHERE clause first,
run it as a SELECT to confirm it matches the right rows, then wrap it in UPDATE.
The safe pattern is below.
The safe UPDATE pattern
Step 1
Write SELECT with the same WHERE clause — confirm the right rows come back
→
Step 2
Count matches — is it the number of rows you expected?
→
Step 3
Replace SELECT … with UPDATE … SET … keeping the same WHERE
→
Step 4
Check "Rows matched" in the output matches your expected count
-- Step 1 & 2: verify which rows will be affected
SELECT book_id, title, price
FROM books
WHERE genre = 'Sci-fi'; -- shows 2 rows: Dune and The Left Hand of Darkness
-- Step 3: now run the update
UPDATE books
SET price = ROUND(price * 0.90, 2)
WHERE genre = 'Sci-fi';
-- Step 4: check the output
Rows matched: 2 Changed: 2 Warnings: 0 -- ✓ matches expected
3. DELETE — Removing Rows
DELETE removes rows from a table. Like UPDATE, it only affects rows that match the
WHERE clause — and without a WHERE clause, it removes every row.
DELETE FROM books
WHERE stock = 0 AND published_year < 1950;
-- Remove a single order by its primary key
DELETE FROM orders
WHERE order_id = 5;
Query OK, 1 row affected (0.01 sec)
-- Remove all out-of-print books with zero stock
DELETE FROM books
WHERE stock = 0
AND published_year < 2000;
DELETE and foreign keys
If a row is referenced by another table via a foreign key, MySQL will block the
delete by default — this is referential integrity in action:
-- Attempt to delete an author who has books
DELETE FROM authors
WHERE author_id = 1; -- Matt Haig has 2 books
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`bookshop`.`books`,
CONSTRAINT `fk_books_author` FOREIGN KEY (`author_id`)
REFERENCES `authors` (`author_id`))
-- Solution: delete the child rows first, then the parent
DELETE FROM books WHERE author_id = 1; -- remove books first
DELETE FROM authors WHERE author_id = 1; -- now the author can go
Apply the same safe pattern as UPDATE. Run the WHERE clause as a
SELECT first to confirm which rows will be deleted. Delete is irreversible outside of
a transaction — getting it wrong means restoring from a backup.
LIMIT on DELETE
You can add LIMIT n to a DELETE to cap how many rows are removed at once.
Useful when cleaning up a large number of rows in batches to avoid locking the table for
too long:
-- Delete old orders in batches of 1000 to avoid long locks
DELETE FROM orders
WHERE order_date < '2020-01-01'
LIMIT 1000;
-- Run this repeatedly until 0 rows affected
4. TRUNCATE vs DELETE — Emptying a Table
Both DELETE FROM table (with no WHERE) and TRUNCATE TABLE table
remove all rows. They behave very differently, though:
| Feature | DELETE (no WHERE) | TRUNCATE TABLE |
|---|
| Speed |
Slow on large tables — deletes row by row, logs each deletion |
Very fast — drops and recreates the data pages |
| Transaction log |
Fully logged — each row delete is recorded |
Minimal logging — only the deallocation is logged |
| Can be rolled back? |
Yes — if inside a transaction |
No — TRUNCATE implicitly commits; can't be rolled back |
| WHERE clause |
Supported — you can filter which rows to delete |
Not supported — always removes all rows |
| AUTO_INCREMENT reset |
Counter keeps its current value |
Counter resets to 1 |
| Foreign key checks |
Enforced — will fail if rows are referenced elsewhere |
Will fail if the table is a parent in an FK relationship |
| Triggers |
Fires DELETE triggers |
Does NOT fire DELETE triggers |
| Use when… |
You need rollback safety, need to fire triggers, or are deleting a subset |
You want to completely wipe a table fast — typically for dev/test resets |
-- Wipe all rows — slow, logged, can be rolled back, keeps AUTO_INCREMENT
DELETE FROM orders;
-- Wipe all rows — fast, cannot be rolled back, resets AUTO_INCREMENT
TRUNCATE TABLE orders;
TRUNCATE resets AUTO_INCREMENT to 1. If any other table holds foreign
keys pointing at the truncated table's primary key, and you then INSERT new rows that
get IDs 1, 2, 3 again, those IDs will now clash with the references in the child table.
In development this is usually fine — you truncate everything and reinsert. In
production it is almost always the wrong choice. Use DELETE there.
5. Upsert — REPLACE and INSERT … ON DUPLICATE KEY UPDATE
Sometimes you want to insert a row if it doesn't exist, or update it if it does.
This is called an upsert. MySQL offers two ways to do it.
INSERT … ON DUPLICATE KEY UPDATE
The most precise upsert: if the INSERT would violate a unique constraint, run the
UPDATE clause instead. Existing rows that don't conflict are inserted normally:
-- Add stock if the book already exists, insert if it doesn't
INSERT INTO books (book_id, title, price, stock)
VALUES (3, 'Dune', 9.99, 10)
ON DUPLICATE KEY UPDATE
stock = stock + VALUES(stock); -- add the 10 to existing stock
-- VALUES(col) refers to the value that was in the attempted INSERT
REPLACE INTO
REPLACE INTO is simpler but more destructive: it DELETEs the conflicting
row and INSERTs a new one. This resets AUTO_INCREMENT IDs and loses any columns you
didn't include in the REPLACE. Use INSERT … ON DUPLICATE KEY UPDATE instead
for precision:
-- REPLACE deletes the old row and inserts a brand new one
-- Any columns not listed get their DEFAULT values (or NULL)
REPLACE INTO books (book_id, title, price, stock)
VALUES (3, 'Dune', 9.99, 30);
-- The old Dune row is gone; a new one is inserted
-- genre and published_year would reset to NULL/DEFAULT!
Chapter Summary
| Statement | What it does | Key rules |
|---|
| INSERT INTO … VALUES |
Add one or more new rows |
Always name the columns. Insert parents before children (foreign keys). Use multi-row VALUES for bulk inserts. |
| INSERT … SELECT |
Insert the result of a SELECT |
Column count and types in the SELECT must match the INSERT column list. |
| UPDATE … SET … WHERE |
Change values in matching rows |
No WHERE = every row updated. Always SELECT-first to verify. SET can reference the current column value. |
| DELETE FROM … WHERE |
Remove matching rows |
No WHERE = every row deleted. Blocked by foreign key constraints. SELECT-first pattern applies. |
| TRUNCATE TABLE |
Remove all rows instantly |
Cannot be rolled back. Resets AUTO_INCREMENT. Does not fire triggers. Use only in dev/test resets. |
| INSERT … ON DUPLICATE KEY UPDATE |
Insert if new, update if exists (upsert) |
Preferred over REPLACE. Only updates the conflicting row; doesn't replace other columns. |
| LAST_INSERT_ID() |
Return the AUTO_INCREMENT value from the last INSERT |
Session-scoped — returns your own last insert, not someone else's concurrent insert. |
Next: Chapter 6 — CREATE TABLE and Schema Design.
Chapter 5 assumed the tables already existed. Chapter 6 goes back to the beginning:
how to create tables from scratch, choose constraints, define primary and foreign keys,
and alter or drop tables when requirements change.