UPDATE without WHERE changed every row: how to undo it

VERICTO-030 Critical In CI (offline CLI): BLOCKED

UPDATE products SET price = 0; sets price to 0 in every row of products: without a WHERE, an UPDATE applies to the whole table. If the transaction is still open, run ROLLBACK now. If it was committed, the old values are only in your backups, point-in-time recovery or, on MySQL, the binary log. Stop writes to the table before you restore.

How to recover the data

  • If the UPDATE ran inside a transaction that is still open, run ROLLBACK. After COMMIT (or with autocommit on), it cannot be rolled back.
  • Stop the writers that change the table, so new legitimate changes do not get overwritten by the restore.
  • Restore a point-in-time copy from just before the statement (PostgreSQL PITR, or the managed service's point-in-time restore) into a separate instance, and copy only the damaged column back by primary key: UPDATE products p SET price = r.price FROM restored_products r WHERE p.id = r.id;
  • On MySQL with row-based binary logging (the 8.0 default), the binlog keeps the before image of each changed row; mysqlbinlog --verbose --base64-output=DECODE-ROWS decodes it so you can rebuild the old values. Otherwise, restore the last backup elsewhere and replay the binlog up to just before the UPDATE.

Why it happens

The WHERE was lost: a query run in a console with the condition on a line that was not selected, a builder that drops an empty filter, an agent or script that generated SET but no condition. The statement is valid SQL, so the database runs it. MySQL has a guard for this, sql_safe_updates, which MySQL Workbench enables by default; with it on, the statement fails with Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

How to fix it

Always scope an UPDATE by a key, and run changes by hand inside a transaction, checking the affected row count before COMMIT:

BEGIN;
UPDATE products SET price = 0 WHERE id = 1;
-- expect UPDATE 1, otherwise ROLLBACK
COMMIT;

On MySQL, keep sql_safe_updates on for interactive sessions instead of turning it off to get past Error 1175.

How to stop it from happening again

VERICTO-030 · UPDATE without WHERE on main table · Severity: Critical · AST node: UpdateStmt > WhereClause = NULL

The statement the rule reports

-- reprice.sql
UPDATE products SET price = 0;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline reprice.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ reprice.sql  BLOCKED  VERICTO-030 critical
    UpdateStmt > WhereClause = NULL
    fix: UPDATE products SET {col} = $1 WHERE id = $2
1 blocked · 0 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

✖ Vericto blocked 1 statement  (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
  ✖ reprice.sql  BLOCKED  VERICTO-030 critical
      UpdateStmt > WhereClause = NULL
      fix: UPDATE products SET {col} = $1 WHERE id = $2

This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
    -- vericto:ignore[VERICTO-030] <reason>   (a reason is required)

The CLI blocks it and exits with code 1, so the CI job fails before the merge.

The corrected version

-- reprice.sql
UPDATE products SET price = 0 WHERE id = 1;
$ npx @vericto/vericto-cli@2.3.0 check --offline reprice.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

Try it free, no account

The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).

npx @vericto/vericto-cli@2.3.0 check --offline migrations/

On every pull request

The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:

on: pull_request
jobs:
  vericto:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
        with: { fetch-depth: 0 }
      - uses: vericto/check-sql@v1
        with: { offline: true, upload-sarif: false }

At runtime, for AI agents

The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.

The AST condition in the engine

The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:

"VERICTO-030" => find(stmts, |s| {
    s.kind == StatementKind::Update && s.where_presence != WherePresence::Present
})
.map(|s| violation(rule, s, suggest_update(s))),

Frequently asked questions