Session 11 — INSERT, UPDATE, DELETE, and transactions

SQL also writes. INSERT adds rows (explicit values or INSERT ... SELECT), UPDATE changes matching rows, and DELETE removes them — all governed by the WHERE clause, without which you rewrite or erase the whole table. Foreign keys police these statements, rejecting broken references. Transactions (BEGIN / COMMIT / ROLLBACK) make multi-step changes all-or-nothing.

Plan: Core ideas → worked DML → Quiz.

Time: about 60–70 minutes if you trace every change against the original tables row by row.

Session 11 — INSERT, UPDATE, DELETE, and transactions

The one-paragraph story

Reading is half of SQL; the other half changes the database. INSERT adds rows, UPDATE changes existing rows, and DELETE removes them — together the DML trio. Each follows one pattern: name the table, say what to do, and be precise about which rows. Precision matters because UPDATE and DELETE act on every row that matches the WHERE clause — forget the WHERE and you rewrite or erase the whole table. Around all three sits the transaction: a unit of work that either commits completely or rolls back completely, so a half-finished change never survives. This session writes real modifications, traces their effects row by row, and shows why transactions make multi-step changes safe.

---

INSERT: adding rows

Two forms. Explicit values:

INSERT INTO books (book_id, title, author_id, genre, price, published_year)
VALUES (16, 'The North Route', 3, 'history', 29.00, 2023);

The column list names where each value goes; order the values to match. If you omit the column list, every column gets a value in table order — fragile, so prefer the explicit list.

Insert the result of a query (a whole set of rows at once):

INSERT INTO sales (sale_id, book_id, qty, sale_date)
SELECT 200, book_id, 5, DATE '2024-02-01'
FROM books
WHERE genre = 'fiction';

One INSERT ... SELECT statement adds one row per selected book — the subquery results become the inserted rows.

---

UPDATE: changing existing rows

UPDATE books
SET price = price * 1.1
WHERE published_year < 2020;

Every row matching WHERE gets price multiplied by 1.1; other rows are untouched. SET can change several columns at once (SET price = 20, genre = 'fiction'). The danger is the default: UPDATE books SET price = 0; (no WHERE) updates every row — correct only when you really mean all of them.

---

DELETE: removing rows

DELETE FROM sales WHERE qty < 2;

Deletes every matching row and only those. DELETE FROM sales; (no WHERE) empties the table. The two-word habit — always check the WHERE — is the difference between "removed the unwanted rows" and "wiped the table".

TRUNCATE TABLE is a faster cousin that empties a table in one shot (and, in most engines, cannot be rolled back). Prefer DELETE when precision or rollback matters.

---

Transactions: all-or-nothing changes

A transaction groups statements so they succeed or fail together:

BEGIN;
UPDATE books SET price = price * 1.1 WHERE genre = 'fiction';
INSERT INTO sales (sale_id, book_id, qty, sale_date)
VALUES (201, 10, 4, DATE '2024-02-01');
COMMIT;

• BEGIN starts the transaction.
• The two statements run; nothing is visible to other users yet.
• COMMIT makes the changes permanent — both or neither survive.

If something goes wrong before COMMIT — a constraint violation, a typo, a power cut — you can ROLLBACK, and the database returns to the state at BEGIN. The guarantee (ACID's 'A' and 'I'): atomicity (all-or-nothing) and isolation (intermediate states invisible). For our drills the rule is simple: begin, do the work, commit when it is right, rollback when it is not.

---

Foreign keys police the changes

Remember the schema's REFERENCES clauses? They are active during DML:

• INSERT a book with author_id = 99 (no such author) → rejected, unless author_id is NULL.
• DELETE an author who still has books → rejected, because books reference her.
• UPDATE an author's author_id that books point at → rejected the same way.

These rules are what keep the joins of Sessions 5–6 from ever encountering dangling references (the orphan problem you simulated with LEFT JOIN). In Session 12 you decide how to react — restrict, cascade, or set NULL.

---

Pitfalls to watch for

• UPDATE/DELETE without WHERE acts on the whole table. Write the WHERE first, then the statement around it.
• INSERT column order must match the value order when you use an explicit list.
• Foreign keys reject broken references — a rejected INSERT/DELETE is the database protecting you, not a bug.
• Uncommitted work can be rolled back — end multi-step changes with an explicit COMMIT, and roll back when you are unsure.

Session 11 — Worked examples

The bookshop data (state before any modification):

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2)

Example A — INSERT one row

INSERT INTO books (book_id, title, author_id, genre, price, published_year)
VALUES (16, 'The North Route', 3, 'history', 29.00, 2023);

The table now has a seventh row. Verify with SELECT * FROM books WHERE book_id = 16; → (16, The North Route, 3, history, 29.00, 2023). Column order matches the value order exactly.

---

Example B — INSERT rejects a dangling reference

INSERT INTO books (book_id, title, author_id, genre, price, published_year)
VALUES (17, 'Orphan Trail', 99, 'fiction', 10.00, 2024);

The foreign key author_id REFERENCES authors(author_id) rejects this: there is no author 99. No row is inserted. The error message is the database refusing to create the exact situation Session 5's LEFT JOIN would have flagged as an orphan.

---

Example C — INSERT ... SELECT

INSERT INTO sales (sale_id, book_id, qty, sale_date)
SELECT 200, book_id, 5, DATE '2024-02-01'
FROM books
WHERE genre = 'fiction';

The subquery returns books 10 and 11 (the fiction books), so two rows are inserted: (200, 10, 5) and (201, 11, 5) — one per selected row. Note the sale ids must not collide with existing ids.

---

Example D — UPDATE with WHERE

UPDATE books
SET price = price * 1.1
WHERE published_year < 2020;

Matching books (2019, 2018, 2017): book 10 24.99 → 27.49, book 12 22.00 → 24.20, book 14 27.50 → 30.25. Books 11, 13, 15 untouched. Verify by selecting book_id, price after the update.

---

Example E — UPDATE without WHERE (the danger)

UPDATE books SET price = 0;

Every row's price becomes 0. If this was a mistake, a transaction saves you:

BEGIN;
UPDATE books SET price = 0;
-- realise the error
ROLLBACK;

The table is back to its pre-BEGIN state. Had you already COMMITed, the damage would be permanent.

---

Example F — DELETE with WHERE

DELETE FROM sales WHERE qty < 2;

Removes sales 101 (qty 1) and 103 (qty 1); sales 100, 102, 104 remain. SELECT * FROM sales; shows three rows.

---

Example G — A full transaction

BEGIN;
INSERT INTO sales (sale_id, book_id, qty, sale_date) VALUES (202, 13, 6, DATE '2024-02-05');
UPDATE books SET price = price * 0.95 WHERE book_id = 13;
COMMIT;

Both statements apply together at COMMIT. If the INSERT had violated a constraint (say a duplicate sale_id), the transaction would fail and the price change would not survive either — atomicity in action.