Modifying Data

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.