Skip to main content
Severity: Warning

​
Overview

These rules flag UPDATE and DELETE statements that lack a WHERE clause. Without a WHERE clause, these statements affect every row in the table, which is rarely intended and can cause catastrophic data loss. Rule names:
  • update-without-where
  • delete-without-where
Default severity: Warning

​
When These Rules Trigger

​
update-without-where

This rule triggers when:
  • An UPDATE statement has no WHERE clause
The rule does not trigger when:
  • UPDATE has a WHERE clause
  • UPDATE uses FROM with a WHERE clause for joins

​
delete-without-where

This rule triggers when:
  • A DELETE statement has no WHERE clause
The rule does not trigger when:
  • DELETE has a WHERE clause
  • DELETE uses USING with a WHERE clause for joins

​
Why It Matters

​
Data Loss Risk

Without a WHERE clause:
  • UPDATE modifies every single row in the table
  • DELETE removes every single row from the table
This is catastrophic if unintended: 
-- Accidentally deactivates ALL users!
UPDATE users SET active = false;

-- Accidentally deletes ALL orders!
DELETE FROM orders;

​
Common Mistakes

  1. Forgetting the WHERE clause: Most common during development or in SQL clients
  2. Wrong statement order: Writing UPDATE table SET col = val and forgetting to add WHERE before executing
  3. Copy-paste errors: Copying a statement and forgetting to update the condition

​
When It’s Intentional

Sometimes you genuinely want to update/delete all rows:
  • Resetting a staging table
  • Bulk status updates
  • Clearing test data
In these cases, it’s better to be explicit: 
-- If you really mean "all rows", use TRUNCATE for DELETE
TRUNCATE TABLE test_data;

-- Or add a WHERE clause that's always true to acknowledge intent
UPDATE settings SET value = 'default' WHERE TRUE;

​
Examples

UPDATE users SET active = false
Issue: Deactivates every single user in the table, likely unintended.
DELETE FROM users
Issue: Deletes every single user from the table. Catastrophic if unintended.
UPDATE users SET active = false WHERE id = 1
Why it’s better: Only affects the specific user with id = 1.
DELETE FROM users WHERE id = 1
Why it’s better: Only deletes the specific user with id = 1.
UPDATE orders SET status = 'shipped' 
FROM shipments 
WHERE orders.id = shipments.order_id
Why it’s better: Uses a join condition to update only matching orders.
DELETE FROM orders 
USING expired 
WHERE orders.id = expired.id
Why it’s better: Uses a join condition to delete only matching orders.
UPDATE settings SET value = 'default' WHERE TRUE
Why it’s acceptable: The WHERE TRUE explicitly signals “yes, I mean all rows.”
TRUNCATE TABLE test_data
Why it’s better: For deleting all rows, TRUNCATE is faster and clearer in intent.

​
Best Practices

​
Always Use WHERE

Make it a habit to write WHERE before executing UPDATE or DELETE: 
-- Start with WHERE, even if it's incomplete
UPDATE users SET active = false WHERE ...

-- Then fill in the condition
UPDATE users SET active = false WHERE last_login < '2020-01-01'

​
Test with SELECT First

Before running UPDATE or DELETE, test your WHERE clause with SELECT: 
-- First, verify which rows match
SELECT id, email FROM users WHERE last_login < '2020-01-01';

-- Then, if correct, update them
UPDATE users SET active = false WHERE last_login < '2020-01-01';

​
Use Transactions

For critical operations, use transactions so you can rollback if something goes wrong: 
BEGIN;

UPDATE users SET active = false WHERE last_login < '2020-01-01';

-- Verify the update
SELECT COUNT(*) FROM users WHERE active = false;

-- If correct:
COMMIT;
-- If wrong:
ROLLBACK;

​
For Intentional Bulk Operations

If you genuinely need to affect all rows: 
-- For DELETE, use TRUNCATE (faster and clearer)
TRUNCATE TABLE staging_data;

-- For UPDATE all rows, make intent explicit
UPDATE config SET version = 2 WHERE TRUE;

​
Implementation Details

​
update-without-where

The rule works by:
  1. Checking if the statement is an UpdateStmt
  2. Checking if WhereClause is nil
  3. Reporting a warning at the start of the statement
Implementation: update_without_where.go

​
delete-without-where

The rule works by:
  1. Checking if the statement is a DeleteStmt
  2. Checking if WhereClause is nil
  3. Reporting a warning at the start of the statement
Implementation: delete_without_where.go

​
How to Disable

You can disable these rules in your pgvet configuration: 
rules:
  update-without-where:
    enabled: false
  delete-without-where:
    enabled: false
Or disable them for specific queries using inline comments: 
-- pgvet: ignore update-without-where
UPDATE settings SET value = 'default'

-- pgvet: ignore delete-without-where
DELETE FROM temp_table

Build docs developers (and LLMs) love

Get started for freeTalk to us