MERGE deleted or updated far more rows than expected

VERICTO-083 High In CI (offline CLI): BLOCKED

MERGE INTO inventory t USING incoming s ON t.sku = s.sku WHEN MATCHED THEN DELETE; deletes every inventory row whose sku appears in incoming. If incoming is a full snapshot rather than a list of changes, that is the whole table. If the transaction is still open, run ROLLBACK; once committed, the rows come back only from a backup or point-in-time recovery.

How to recover the data

  • If the MERGE ran inside a transaction that is still open, run ROLLBACK.
  • Stop the job that runs the MERGE so the next run does not repeat it.
  • Restore a point-in-time copy from just before it into a separate instance and copy the deleted or overwritten rows back by primary key.

Why it happens

MERGE joins a source to a target and applies actions to every match, so its reach depends entirely on what is in the source and on the ON condition. A source that was loaded twice, a staging table that was not emptied, or a join on a non-unique column turns a small sync into a mass change.

How to fix it

Count the matches before running it, and put conditions on the actions themselves:

SELECT count(*) FROM inventory t JOIN incoming s ON t.sku = s.sku;

MERGE INTO inventory t USING incoming s ON t.sku = s.sku
WHEN MATCHED AND s.discontinued THEN DELETE;

Run it inside a transaction and check the affected row count (PostgreSQL reports MERGE n) before COMMIT.

How to stop it from happening again

VERICTO-083 · MERGE · Severity: High · AST node: MergeStmt

The statement the rule reports

-- sync.sql
MERGE INTO inventory t USING incoming s ON t.sku = s.sku WHEN MATCHED THEN DELETE;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline sync.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.
✖ sync.sql  BLOCKED  VERICTO-083 high
    MergeStmt
    fix: Scope the MERGE join so it cannot match the whole table
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))
  ✖ sync.sql  BLOCKED  VERICTO-083 high
      MergeStmt
      fix: Scope the MERGE join so it cannot match the whole table

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-083] <reason>   (a reason is required)

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

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-083" => find(stmts, |s| s.kind == StatementKind::Merge)
    .map(|s| {
        let mut v = violation(
            rule,
            s,
            Some("Scope the MERGE join so it cannot match the whole table".to_string()),
        );
        v.ast_node_path = "MergeStmt".to_string();
        v
    }),

Frequently asked questions