INSERT, UPDATE, DELETE: writing in the notebook

So far you have been a very polite guest. Every query you wrote read the notebook and put it back exactly as found. SELECT, WHERE, GROUP BY: all reading. Tonight the chef hands you the pen. Three statements write into a table: INSERT adds rows, UPDATE changes them, DELETE removes them. They are short and simple, and one careless keystroke can empty a whole table, so this lesson is half syntax, half aim.

Three ways to write

Here they are, doing their thing to the orders table on repeat:

INSERT INTO orders VALUES ('gunkan', 3, 5);
UPDATE orders SET qty = 2 WHERE item = 'nigiri';
DELETE FROM orders WHERE item = 'temaki';

fig 1 · insert adds a row, update rewrites one, delete removes one

Watch the loop. A new row slides in, a number gets rewritten in place, a row fades to a ghost and is gone. That is the entire toolbox. Everything else in this lesson is about pointing it at the right rows.

INSERT adds a row

INSERT INTO names the table, VALUES hands over one new row. The values land in the columns in order: first value into the first column, and so on down the line:

INSERT INTO orders VALUES ('gunkan', 3, 5);

You can also name the columns yourself:

INSERT INTO orders (item, qty, tbl) VALUES ('gunkan', 3, 5);

More typing, but safer. If the table ever changes shape, the named version still puts every value where it belongs. INSERT is the gentle one of the three: it only ever adds, it cannot touch a row that already exists.

UPDATE rewrites, DELETE removes

UPDATE changes rows that are already there. SET says what changes, WHERE says which rows it happens to:

UPDATE orders SET qty = 2 WHERE item = 'nigiri';

Table 3 wants a second nigiri, so the row is rewritten in place. Same row, new number. DELETE is even shorter, because it takes no columns at all. The whole row goes:

DELETE FROM orders WHERE item = 'temaki';

Forget the WHERE and you hit every row

Remember the torii gate from lesson 02? WHERE asks every row a yes/no question, and only the yes rows are touched. Now picture the gate removed: every row answers yes. For a SELECT that just means a big result, no harm done. For UPDATE and DELETE it means every row gets rewritten or removed:

UPDATE orders SET qty = 99;  -- every order is now 99 pieces
DELETE FROM orders;          -- every order is gone

Both statements are perfectly legal SQL. The database will not warn you, ask you, or hesitate. It assumes you meant it. This is the single most famous way to ruin an afternoon with SQL, and now you have seen it coming.

Read it back with SELECT

Writes are quiet. Run an UPDATE and the database answers with something like “1 row affected”, not with the row itself. So make this a reflex: after every write, read it back:

UPDATE orders SET qty = 2 WHERE item = 'nigiri';
SELECT * FROM orders WHERE item = 'nigiri';

Even better, flip the order for the risky ones. Before an UPDATE or DELETE, run the exact same WHERE as a SELECT first. The rows that come back are precisely the rows your write will hit. If the preview surprises you, you just saved the table.

Try this yourself

Open your playground and set the notebook up fresh. This one always works:

CREATE TABLE orders (item TEXT, qty INTEGER, tbl INTEGER);
INSERT INTO orders VALUES ('maki', 2, 5), ('nigiri', 1, 3), ('temaki', 1, 5);
SELECT * FROM orders;

Three rows back, notebook ready. Now work the pen:

Add the gunkan order from fig 1 with INSERT, then SELECT * FROM orders; to see four rows.
Table 3 orders a second nigiri: UPDATE its qty to 2, and read it back.
Table 5 cancels the temaki: DELETE that row, and read it back.
The brave finale: run DELETE FROM orders; on purpose and watch the table empty out. It is a playground, nothing of value was lost. Then rebuild it with the block above, because now you know how.