UPDATE & DELETE — Modifying Data

INSERT adds rows, SELECT reads them — UPDATE and DELETE are how your engine mutates existing state. Both reuse the WHERE evaluator you already built, applying it not to decide what to display, but to decide what to change.

UPDATE

UPDATE users SET age = 31 WHERE name = 'Alice'

The structure is: table name, a SET clause (one or more column = value assignments), and an optional WHERE. Parsing the SET list is similar to parsing the column list in CREATE TABLE — a comma-separated sequence:

SET age = 31, name = 'Alicia'

Execution walks every row in the table, evaluates the (optional) WHERE predicate, and for each row that matches, applies every assignment in the SET list:

def execute_update(table, set_list, where, schema):
    count = 0
    for row in table.rows:
        if where is None or eval_expr(where, row, schema):
            for col_name, new_value in set_list:
                idx = find_column_index(schema, col_name)
                type_check(schema.columns[idx], new_value)   # reuse INSERT's type checking
                row.values[idx] = new_value
            count += 1
    return count

Two behaviors matter here:

• No WHERE means "all rows." UPDATE users SET age = 0 (no WHERE) must touch every row in the table — don't require a predicate.
• Type-check SET values the same way INSERT checks VALUES. Assigning a string into an INTEGER column should be rejected the same way an out-of-type INSERT is rejected — reuse the column-type validation logic from the INSERT lesson rather than writing a second copy.

The result is OK <count>, where count is the number of rows actually modified — this return value is your affected-row count, and it must reflect matched rows even when some SET assignments happen to set a column to the same value it already had.

DELETE

DELETE FROM users WHERE age < 20

Deletion has the same "walk, evaluate predicate, act" structure as UPDATE, except the action is removal rather than mutation. The subtlety is how you remove while iterating — mutating a list you're iterating over by index tends to skip elements or throw off indices. Two safe patterns:

# Option A: build a new list of survivors
table.rows = [r for r in table.rows if not (where is None or eval_expr(where, r, schema))]

# Option B: collect matches first, then remove
to_delete = [r for r in table.rows if where is None or eval_expr(where, r, schema)]
count = len(to_delete)
for r in to_delete:
    table.rows.remove(r)

The first option is simpler and avoids the iterate-while-mutating trap entirely — filter once, keep the ones that don't match, and the count is original_length - new_length.

Just like UPDATE, no WHERE clause means "delete all rows," and the output is OK <count> for the number of rows removed.

Edge cases

• Rows that don't match the predicate must be left completely untouched in both statements — verify with a query afterward that unaffected rows kept their original values.
• UPDATE/DELETE on an unknown table should produce the same ERR unknown table: <name> used elsewhere, checked before attempting to scan any rows.
• If a SET clause references an unknown column, or a WHERE clause references an unknown column, surface ERR unknown column: <name> rather than silently ignoring the assignment or condition.
• If rowid was introduced in an earlier lesson, DELETE must not reuse deleted rowids for future inserts — the auto-increment counter should keep climbing regardless of deletions.