Format a SQL UPDATE with WHERE

UPDATE statements are among the most dangerous SQL commands to write incorrectly because a missing or misconfigured WHERE clause will modify every row in the table with no easy way to undo the change. A production UPDATE on a table with millions of rows that should have touched one record but instead touched all of them is the kind of incident that generates a post-mortem. Formatting the query before executing it makes the WHERE clause highly visible and easy to review. This example updates a user record with four fields: renaming the user, changing their email, elevating their role to admin, and updating the timestamp. The WHERE clause includes three conditions: matching by id, confirming the account is active, and ensuring the record hasn't been soft-deleted. This multi-condition WHERE pattern is a defensive coding practice — it provides extra safety rails that prevent accidentally updating inactive or deleted accounts. The formatted version places each SET column on its own line with consistent indentation, making it trivial to audit the update list. It also clearly separates the WHERE conditions on individual lines with the AND keyword at the beginning, making it easy to read each condition independently. Compare this to a minified single-line UPDATE and the difference in reviewability is dramatic. Soft deletes deserve a mention: many production databases don't actually delete records — they set a deleted_at timestamp and filter it out with WHERE deleted_at IS NULL in queries. This preserves audit history, enables undo functionality, and prevents foreign key constraint violations. The AND deleted_at IS NULL in this example's WHERE clause is a standard pattern in soft-delete schemas. Safety practices before running production UPDATEs: first run the same WHERE clause as a SELECT COUNT(*) to verify how many rows will be affected. If the number is unexpected, stop before running the UPDATE. Many database clients and ORMs support transaction-wrapped updates where you can check the affected row count before committing. Some database configurations have safe-update mode that prevents updates without a WHERE clause. Real-world scenarios: an admin panel action that elevates a user's role and notifies them by email; a background job that marks expired subscriptions as inactive; a data correction script that fixes specific records after a bug was found in import logic. Tips: log all production UPDATE operations with the query, affected row count, and executing user for audit trail purposes.

UPDATE users
SET
  name = 'Jane Doe',
  email = '[email protected]',
  role = 'admin',
  updated_at = CURRENT_TIMESTAMP
WHERE id = 42
  AND active = 1
  AND deleted_at IS NULL;