Chapter 14: Updating and Deleting Data
Up until now, we’ve been creating tables, inserting data, and reading it with SELECT. But real databases are alive — prices change, stock gets updated, customers cancel orders, old records get archived… That’s what UPDATE and DELETE are for.
We’ll also cover transactions — the safety net that lets you say: “Either do all these changes perfectly, or undo everything if something goes wrong.”
We’ll use our familiar bookshop database. Let’s quickly remind ourselves of the books table (with a few extra columns for realism):
| book_id | title | author_id | price | stock | is_available | last_updated |
|---|---|---|---|---|---|---|
| 1 | The Room on the Roof | 1 | 250.00 | 45 | 1 | 2026-01-01 10:00:00 |
| 2 | 2 States | 3 | 299.00 | 120 | 1 | 2026-01-02 11:00:00 |
| 3 | The White Tiger | 2 | 399.00 | 35 | 1 | 2026-01-03 12:00:00 |
| 4 | Wise and Otherwise | 5 | 199.00 | 80 | 1 | 2026-01-04 13:00:00 |
| 5 | The Immortals of Meluha | 4 | 349.00 | 65 | 1 | 2026-01-05 14:00:00 |
1. The UPDATE Statement – Changing Existing Data
UPDATE lets you modify one or more columns in existing rows.
Basic Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE condition; |
The WHERE clause is CRITICAL! Without it, ALL rows will be updated — a very common and very dangerous mistake!
Example 1 – Increase price of all books by 10%
|
0 1 2 3 4 5 6 7 8 |
UPDATE books SET price = price * 1.10; -- WARNING: This updates EVERY book! (We’ll fix this later) |
Example 2 – Safely update price of only one book
|
0 1 2 3 4 5 6 7 8 |
UPDATE books SET price = 275.00 WHERE book_id = 1; -- Only "The Room on the Roof" |
Example 3 – Update multiple columns + use calculation
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE books SET price = price * 1.15, -- 15% price increase last_updated = CURRENT_TIMESTAMP, -- record when changed stock = stock - 5 -- sold 5 copies WHERE book_id = 2; -- Only "2 States" |
Example 4 – Update based on condition (multiple rows)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Give 20% discount on all books with stock > 100 UPDATE books SET price = price * 0.80, is_available = 1 WHERE stock > 100; |
Example 5 – Update using a subquery (advanced but common)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Set price to average price of all books by the same author UPDATE books b SET price = ( SELECT ROUND(AVG(price), 2) FROM books b2 WHERE b2.author_id = b.author_id ) WHERE book_id = 5; -- Update only one book as example |
Best Practice Rule #1: ALWAYS write and test your WHERE clause first with SELECT!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- First check which rows will be affected SELECT title, price, stock FROM books WHERE stock > 100; -- Then run the UPDATE UPDATE books SET price = price * 0.90 WHERE stock > 100; |
2. The DELETE Statement – Removing Rows
DELETE removes entire rows from a table.
Syntax:
|
0 1 2 3 4 5 6 7 |
DELETE FROM table_name WHERE condition; |
Again — WHERE is CRITICAL! No WHERE = delete everything!
Example 1 – Delete one specific book
|
0 1 2 3 4 5 6 7 |
DELETE FROM books WHERE book_id = 10; -- "The Palace of Illusions" sold out forever |
Example 2 – Delete all books with low stock
|
0 1 2 3 4 5 6 7 |
DELETE FROM books WHERE stock < 20; |
Example 3 – Delete books older than 20 years
|
0 1 2 3 4 5 6 7 |
DELETE FROM books WHERE published_year < YEAR(CURRENT_DATE) - 20; |
Example 4 – Delete using JOIN (delete from one table based on another)
|
0 1 2 3 4 5 6 7 8 9 |
DELETE b FROM books b INNER JOIN authors a ON b.author_id = a.author_id WHERE a.country = 'USA'; -- Remove all books by US authors |
Best Practice Rule #2: ALWAYS preview with SELECT first!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Check what will be deleted SELECT title, stock FROM books WHERE stock < 20; -- Then delete DELETE FROM books WHERE stock < 20; |
Important:
- DELETE removes rows but keeps the table structure (columns, indexes, constraints)
- If you want to delete all rows but keep structure faster, use TRUNCATE TABLE (from Chapter 4) — but it cannot be rolled back in most databases.
3. Transactions – The Safety Net (BEGIN, COMMIT, ROLLBACK)
A transaction is a group of SQL statements that are treated as one single unit: All succeed, or none succeed.
Basic commands:
- BEGIN or START TRANSACTION → start a transaction
- COMMIT → save all changes permanently
- ROLLBACK → undo everything since BEGIN
Real-life example – Bulk price update with safety:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
START TRANSACTION; -- or BEGIN; -- First update: 10% increase for popular books UPDATE books SET price = price * 1.10 WHERE stock > 80; -- Second update: 5% discount for low-stock books UPDATE books SET price = price * 0.95 WHERE stock < 50; -- Check if everything looks good (you can run SELECT here) SELECT title, price, stock FROM books LIMIT 5; -- If happy → make it permanent COMMIT; -- If something wrong → undo everything! -- ROLLBACK; |
What happens if power fails or error occurs mid-transaction? Most databases automatically ROLLBACK incomplete transactions.
Important in production:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
START TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 123; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 456; -- If both succeed COMMIT; -- If any problem (e.g., account 456 doesn't exist) ROLLBACK; |
ACID Properties (why transactions matter):
- Atomic: All or nothing
- Consistent: Rules (constraints) always respected
- Isolated: Other users don’t see half-done changes
- Durable: Once COMMIT, changes survive crashes
Full Realistic Workflow Example
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
-- Real-world: Restock and adjust price for a book START TRANSACTION; -- Increase stock (5 new copies arrived) UPDATE books SET stock = stock + 5, last_updated = CURRENT_TIMESTAMP WHERE book_id = 3; -- If stock now > 50, give small discount UPDATE books SET price = price * 0.98 WHERE book_id = 3 AND stock > 50; -- Check result SELECT title, price, stock, last_updated FROM books WHERE book_id = 3; -- All good? Save forever COMMIT; -- Oops! Wrong book? Undo! -- ROLLBACK; |
