UPDATE and DELETE: Changing Stored Rows

After this lesson the engine can change and remove rows: UPDATE t SET col = value [, ...] [WHERE ...] and DELETE FROM t [WHERE ...], each answering OK <n> with the number of rows it touched. Both reuse the WHERE evaluator from lessons 4 to 7, but for a new purpose: not "what do I print" but "what do I change".

Same shape as SELECT, different action

UPDATE name SET col = literal { , col = literal } [ WHERE expr ] [ ; ]
DELETE FROM name [ WHERE expr ] [ ; ]

Parse the whole statement first (a syntax error changes nothing), then look names up, then walk the rows. For each row the WHERE tree is evaluated against the row as it was before this statement started, and only a true result selects the row. A row where the filter is unknown (a NULL got involved) is left alone, in both statements:

rows: v = 1, v = NULL, v = 5
DELETE FROM t WHERE NOT (v > 1)    removes only the row with v = 1
                                   (for NULL: NOT unknown = unknown, kept)

With no WHERE every row is selected.

Check everything, then change anything

An UPDATE can fail in several ways, and every one of them must be found before the first row is modified:

1. the statement does not parse: ERR syntax error
2. unknown table: ERR unknown table: <name>
3. a SET column that is not declared: ERR unknown column: <name>. The hidden rowid cannot be assigned: ERR cannot assign to rowid
4. a value of the wrong type for its column (NULL is always allowed): ERR type mismatch for column <name>
5. an unknown column in the WHERE clause: ERR unknown column: <name>

If you checked a type while updating, the first rows would already be changed when the third column fails, and the table would be left in a state no statement asked for. A statement either happens completely or not at all. This is the seed of atomicity, which the transaction chapter returns to.

The count

OK n reports how many rows the WHERE clause selected. An UPDATE that sets a column to the value it already has still counts the row, and one that selects nothing prints OK 0. Rows are matched against their old values, so UPDATE t SET a = 5 WHERE a = 1 changes the single row that had 1 even if another row already holds 5; there is no cascade.

Removing rows while walking them

Deleting from a list you are iterating is a classic source of skipped elements, since removing one shifts the rest. Two safe approaches: build a new list containing only the rows that survive and swap it in, or mark the matches first and remove them afterwards. The count of removed rows is the old length minus the new one.

rowid keeps climbing

Deleting a row does not give its number back. After rows 1 to 3 are all deleted, the next insert gets rowid 4. The counter is not touched by DELETE; and since you walk t.rows rather than recomputing the number from its length, it does not need to be.

Common mistakes

• Validating while updating. A type error found on the second SET column must leave the first untouched.
• Letting unknown select rows. DELETE ... WHERE NOT (v > 1) must not remove NULL rows.
• Counting changed values instead of matched rows.
• Renumbering after a delete.

Your exercise

The starter has everything else, including real_col, which resolves a SET column and rejects rowid. You write update_rows and delete_rows. The mistake to avoid: modifying any row before every check has passed.