UPDATE — Modifying Existing Rows

Every single row in the table is updated. Without WHERE, there is no filter — the SET clause applies to all rows universally. UPDATE customers SET loyalty_tier = 'Bronze' with no WHERE condition changes every customer's loyalty tier to 'Bronze', regardless of what it was before. Gold customers, Platinum customers, Silver customers — all become Bronze simultaneously.

The damage is immediate and permanent without a transaction. If the UPDATE was not wrapped in a transaction with an uncommitted COMMIT, the change cannot be undone — there is no built-in undo in SQL. Recovery requires restoring from a backup (which loses all changes made after the backup was taken) or manually reconstructing the data from logs (which is time-consuming and error-prone).

Prevention: always include a WHERE clause, always run the equivalent SELECT first to verify which rows will be affected, and always wrap consequential UPDATEs in a transaction so ROLLBACK is available if the result is wrong. Many SQL clients have a "safe mode" that prevents UPDATE and DELETE without WHERE — enable it for interactive sessions on production databases. Some teams require a WHERE clause that always includes the primary key for any single-row UPDATE, and a code review for any UPDATE that affects more than 1,000 rows.

In PostgreSQL, use UPDATE ... FROM syntax. The FROM clause specifies additional tables to join, and those tables can be referenced in both the SET and WHERE clauses. UPDATE orders AS o SET total_amount = item_totals.sum FROM (SELECT order_id, SUM(line_total) AS sum FROM order_items GROUP BY order_id) AS item_totals WHERE o.order_id = item_totals.order_id. The subquery or table in FROM is joined to the target table using the WHERE clause.

In MySQL, use UPDATE ... JOIN syntax. The JOIN is declared between the UPDATE and SET: UPDATE orders o JOIN order_items_summary s ON o.order_id = s.order_id SET o.total_amount = s.total WHERE o.total_amount <> s.total. The JOIN condition is part of the UPDATE statement itself, not in a WHERE clause.

A correlated subquery in SET also works in both databases: UPDATE stores SET monthly_target = (SELECT AVG(total_amount) * 30 FROM orders WHERE orders.store_id = stores.store_id). The subquery references the outer table's current row via stores.store_id — this is evaluated once per row being updated. Correlated subqueries are more portable (work in both PostgreSQL and MySQL) but can be slower than the FROM/JOIN approach for large tables because the subquery runs once per row.

The SELECT-before-UPDATE pattern means running a SELECT with the exact same WHERE clause as your planned UPDATE, inspecting the results, verifying the row count and values are correct, and only then executing the UPDATE. It is the single most effective practice for preventing unintended updates.

The reason it matters: UPDATE errors are silent until they cause visible damage. A WHERE condition with a subtle bug — wrong column, wrong value, missing AND — returns results when run as a SELECT, but the wrong results. If you UPDATE directly, you discover the bug only after the damage is done. If you SELECT first, you discover the bug in a read-only context where no harm is done.

The concrete workflow: (1) Write the UPDATE statement. (2) Change UPDATE table SET column = value to SELECT * FROM table — keep the WHERE clause identical. (3) Run the SELECT. Count the rows — does the count match your expectation? Inspect a sample — do these rows look like the ones you meant to update? (4) If yes, change back to UPDATE and run. (5) Verify again with SELECT after the UPDATE. This adds 30–60 seconds to every UPDATE. It has saved careers by preventing accidental mass updates on production databases.

Multiple columns are updated in a single UPDATE statement by listing them all in the SET clause separated by commas: UPDATE employees SET role = 'Senior Manager', salary = 75000, department = 'Management' WHERE employee_id = 5. All assignments in the SET clause are evaluated using the row's values before any change is made — then all changes are applied simultaneously. This means SET col1 = col2, col2 = col1 correctly swaps the values rather than both becoming the original col2 value.

Single-statement multi-column UPDATE is better than separate UPDATE statements for three reasons. First, atomicity: all column changes happen in one transaction. If the UPDATE succeeds, all columns are changed. If it fails, none are. Separate UPDATEs can result in a partial state where some columns changed and others did not — an inconsistent intermediate state. Second, performance: the database scans the table once, applies all changes, and updates all indexes once. Separate UPDATEs scan the table N times and update indexes N times. Third, correctness: in a concurrent system, another session might read the row between two separate UPDATEs, seeing a state where some columns are old and some are new — a visibility anomaly. One UPDATE prevents this.

The only reason to use separate UPDATE statements is when each has a different WHERE clause — when different rows need different sets of columns updated. In that case, consider whether a single UPDATE with CASE WHEN in the SET clause can handle all cases in one statement, which is even more efficient.

Updating millions of rows in a single UPDATE statement holds a lock on those rows for the duration of the operation — potentially minutes or hours. During this time, other queries that need to read or write those rows are blocked, causing application timeouts and degraded performance. If the UPDATE fails, the rollback takes equally long. For tables actively used by a production application, a long-running UPDATE is essentially an outage.

The safe approach is batch updating — processing rows in chunks. Run the UPDATE with a LIMIT (or equivalent) of 1,000 to 10,000 rows per batch, commit after each batch, and repeat until zero rows are affected. Each batch holds locks for only a short time (milliseconds to seconds), commits immediately, and is individually rollbackable. The WHERE clause must be written so each successive batch picks up where the previous left off — typically by including AND updated_column <> new_value so already-updated rows are excluded from subsequent batches.

Batching is typically done from application code or a migration script in a loop: run the UPDATE, check the affected row count, sleep briefly to give other queries a chance to run, repeat until count is 0. For very large tables at companies like Amazon or DoorDash (billions of rows), even batching may not be sufficient — the alternative is a blue-green table migration: create a new table with the correct data, swap the table name atomically, and drop the old table. This approach has zero downtime but is operationally more complex and requires careful application code management during the swap window.

Cause: The WHERE clause was less specific than intended, matching far more rows than expected. Common causes: a missing AND condition that would have narrowed the scope, a typo in the filter value that made it match broadly, or using OR when AND was intended. This is the most damaging class of UPDATE error because it is silent — no error is thrown, just far too many rows changed.

Fix: Immediately wrap the situation in a ROLLBACK if you are inside a transaction. If not in a transaction, assess the damage: SELECT * FROM table WHERE updated_at >= NOW() - INTERVAL '5 minutes' to see what was just changed. Restore from backup if the data cannot be reconstructed. Prevention: always run SELECT with the identical WHERE before UPDATE, verify the row count matches expectation, and run consequential UPDATEs inside a transaction.

Cause: The column name in SET is misspelled or does not exist. Unlike WHERE (which causes 0 rows affected when wrong), a misspelled column name in SET throws an error immediately — which is actually the better failure mode. This typically happens with typos, using the wrong table's column name, or referencing a column that was renamed or not yet created.

Fix: Check the exact column names: SELECT column_name FROM information_schema.columns WHERE table_name = 'customers' ORDER BY ordinal_position. Correct the spelling in SET. If the column genuinely does not exist and needs to be created, use ALTER TABLE ADD COLUMN first. If you meant a column from a different table, ensure the UPDATE FROM syntax references the correct table.

Cause: The WHERE clause matched zero rows. The UPDATE ran successfully but found no rows matching the condition. Common causes: the filter value does not match what is stored (wrong case, extra whitespace, wrong ID), the row was already deleted or updated by another process, a date range that excludes all rows, or a typo in the WHERE column name that PostgreSQL treated as a literal string comparison.

Fix: Run the equivalent SELECT: SELECT * FROM table WHERE [same WHERE condition]. If it returns 0 rows, investigate why. Check the actual stored values: SELECT DISTINCT column FROM table LIMIT 10 to see what values exist. Check for case sensitivity: WHERE LOWER(city) = LOWER('bangalore'). Check for whitespace: WHERE TRIM(email) = 'value'. Once you understand why the WHERE matched nothing, correct the condition and retry.

Cause: You are trying to update a primary key column (customer_id) in customers that is referenced by foreign keys in the orders table. Changing the PK value would orphan all the orders that reference the old value. The FK constraint correctly prevents this. This happens when developers try to change an ID that was set incorrectly, or when migrating data between systems with different ID schemes.

Fix: Never update primary key columns after rows have been inserted — primary keys should be immutable. If the ID was genuinely set wrong, the correct fix is: (1) insert a new row with the correct ID, (2) update all FK references (orders, order_items, etc.) to point to the new ID, (3) delete the old row. This must be done in a transaction. For production systems where IDs are used externally (in URLs, in partner systems), coordinate the ID change carefully — it affects more than just the database.

Cause: The UPDATE is modifying millions of rows without a restrictive WHERE clause (or with a WHERE clause that matches most of the table), is holding a lock for the duration, and may be causing other queries to queue behind it. The slow execution time means other application queries are timing out waiting for the lock to release. On tables with many indexes, each row update also triggers index updates — multiplying the work.

Fix: Use batch updating: add LIMIT to the UPDATE and run it in a loop until 0 rows are affected. This keeps each batch small (1,000–10,000 rows), commits quickly, and releases locks between batches. If the table has many indexes, consider dropping non-essential indexes before the bulk UPDATE and recreating them after — index maintenance is often the largest cost in a bulk UPDATE. Check the query plan with EXPLAIN ANALYZE to confirm the WHERE clause is using indexes. If no WHERE index exists, adding one before the UPDATE can dramatically speed it up.

FreshCart's quarterly loyalty review has three updates to apply: (1) Upgrade any Bronze customer who joined before 2022 to Silver. (2) Upgrade any Silver customer from Seattle or Austin to Gold. (3) Apply a 5% price increase to all Staples products that are currently in stock. For each update, first write the SELECT to verify which rows will be affected, then write the UPDATE.