UPDATE & DELETE — Modifying Data

SELECT reads data; INSERT adds data. Now we complete the four CRUD operations with UPDATE (modify) and DELETE (remove).

UPDATE Syntax

UPDATE users SET age = 31 WHERE name = 'Alice'
UPDATE users SET name = 'Robert', age = 26 WHERE id = 2

UPDATE modifies existing rows in place. The SET clause specifies which columns to change and their new values. The WHERE clause selects which rows to modify.

DELETE Syntax

DELETE FROM users WHERE age < 20
DELETE FROM users    -- deletes ALL rows (no WHERE = match everything)

Counting Affected Rows

Both commands report how many rows were changed:

UPDATE users SET age = 31 WHERE name = 'Alice'
→ OK 1

DELETE FROM users WHERE age < 20
→ OK 3

This count is important — applications use it to verify their operations worked as expected. OK 0 means the WHERE matched nothing.

Implementation Strategy

UPDATE is essentially a filtered scan with modification:

for each row in table:
    if WHERE evaluates to true:
        for each SET assignment:
            row[column] = new_value
        count += 1
return count

DELETE is similar but removes instead of modifying:

rows_to_keep = []
for each row in table:
    if WHERE evaluates to false:
        rows_to_keep.append(row)
    else:
        count += 1
table.rows = rows_to_keep
return count

Type Checking on SET

The new values in SET must match the column type:

UPDATE users SET age = 'old' WHERE id = 1
-- ERR type mismatch for column age

Without WHERE

Both UPDATE and DELETE without WHERE affect all rows:

UPDATE users SET age = 0      -- sets every row's age to 0
DELETE FROM users              -- empties the table

This is by design but dangerous. Real databases often have safeguards (like MySQL's sql_safe_updates mode).

How Databases Handle DELETE Internally

SQLite marks deleted rows in the B-tree and reclaims space during VACUUM. PostgreSQL doesn't delete rows at all — it marks them as "dead" and reclaims them during VACUUM or autovacuum. This is fundamental to PostgreSQL's MVCC design: old row versions are needed by concurrent transactions.

Your Task

Implement UPDATE with SET (single and multiple columns), DELETE with WHERE, affected-row counting, and type checking on SET values.