UPDATE & DELETE: The WHERE Discipline

# UPDATE & DELETE: The WHERE Discipline Reads forgive mistakes; writes don't. Every destructive statement earns a ritual. ```sql -- Ritual: SELECT first, with the same WHERE SELECT * FROM stg_orders WHERE status = 'test'; -- Then, and only then, write with the identical WHERE UPDATE stg_orders SET status = 'new' WHERE status = 'test'; DELETE FROM stg_orders WHERE status = 'test' AND ordered_at < now() - interval '30 days'; ``` ## Rules 1. **SELECT-first, always.** Write the WHERE as a SELECT, eyeball row count, then convert to UPDATE/DELETE. No bare `UPDATE t SET ...` without WHERE — ever, in any environment. 2. **Prefer soft deletes in marts** (`is_deleted`, `deleted_at`) over DELETE: history survives, SCD logic stays simple, and recovery is an UPDATE. 3. **Batch large changes** (`... WHERE id BETWEEN a AND b LIMIT 10000` in a loop) to avoid long locks and WAL explosions on big tables. 4. **In pipelines, UPDATE/DELETE are code smells** — they signal mutable sources. Prefer append-only staging + rebuilt marts; reserve in-place writes for true corrections with an audit column (`updated_at`, `updated_by`).