Adding, changing, removing

INSERT puts rows in

INSERT INTO books (title, author, year) VALUES ('Dune', 'Frank Herbert', 1965); names the columns it is filling and hands over the values in the same order. One statement can carry several rows: after VALUES, list more parentheses, separated by commas.

Naming the columns is the habit worth keeping. Without the list, the values must line up with the table's storage order — which is invisible in the query, and quietly wrong the day a column is added.

UPDATE and DELETE need a WHERE

UPDATE books SET year = 1965 WHERE title = 'Dune'; changes the rows the condition keeps, and only those. DELETE FROM books WHERE year < 1960; removes the rows it matches. Both read a table and a condition: what to change, and which rows are the ones you mean.

Without a WHERE, both apply to every row in the table — which is occasionally the intent and more often the mistake everyone makes once. The habit that prevents it: write the SELECT with the same condition first, look at what it returns, and change it into the write you meant.

Every write reports how many rows it touched, and that number is worth reading. A delete that reports zero has not failed — it has matched nothing, which usually means the condition is wrong rather than the data being gone. The count is the cheapest check that the statement did what you described, and it is there before the next statement runs.

A run is a fresh start here

Every Run in this course rebuilds the database from setup.sql before executing query.sql. Data you insert is gone on the next run, and updates do not accumulate — which makes the examples safe to experiment with, and makes the write statements behave the same today and tomorrow.

A real database does not work that way, and the same statements are used there — only the safety net of the rebuild is missing. That is the reason for the WHERE habit, not a reason to distrust the statements.

A write is all-or-nothing

A single INSERT, UPDATE or DELETE runs as a transaction even when you do not ask for one: either the whole statement takes effect or none of it does. If a constraint fails halfway through an UPDATE, the rows already changed by that statement are put back.

Statements in a file are separate transactions by default, so a later failure does not undo the earlier statements. Wrap a group in BEGIN and COMMIT when they must succeed together, and use ROLLBACK when you want to abandon the group — that is the safety net a real database gives you, and the one this course's rebuild gives you for free.

A transaction is also why a write can be interrupted safely: the database keeps the old version until the commit, so a crash leaves either the before or the after, never half of each.

What a write sees

UPDATE reads the row as it was at the start of the statement. SET year = year + 1 uses the old year for each row, not a value another row just received, so every row moves exactly once. That is what makes an arithmetic update safe.

A DEFAULT fills a column when the INSERT omits it; an explicit NULL is a value and is stored as one. The same distinction decides whether a NOT NULL column can be left out: it can, if a default supplies the value.

Constraints are checked as each row is written. An INSERT of five rows that fails on the fourth writes none of them, because the statement is one transaction — the count is the row count you asked for, or nothing.

The three writes, each with its condition

INSERT INTO books (title, author, year) VALUES ('The Word for World is Forest', 'Ursula K. Le Guin', 1972);

UPDATE books SET year = 1966 WHERE title = 'Dune';

DELETE FROM books WHERE year < 1960;

Try these next

• Write the SELECT for a condition first, then turn that same condition into a DELETE.
• Copy the rows you are about to delete into an archive table with INSERT INTO ... SELECT.
• Run the same UPDATE twice and explain why year = year + 1 is not idempotent.