UPDATE · SQL

UPDATE is the SQL statement that modifies values in existing rows of a table. It names the target table, assigns new values to one or more columns with a SET clause, and limits the affected rows with a WHERE clause; rows that do not match the condition stay unchanged. Together with INSERT and DELETE, the SQL UPDATE statement belongs to the data manipulation language (DML).

UPDATE never adds or removes rows. It changes column values in place, and it runs within a transaction, so the change becomes visible to other sessions only after commit and can be undone with a rollback until then.

Syntax and evaluation of UPDATE

The basic form is UPDATE products SET price = price * 1.05, updated_at = CURRENT_TIMESTAMP WHERE category_id = 7;. The database first identifies the qualifying rows with the WHERE condition, then computes the new values for each row and writes them. Expressions on the right side of SET read the old values of the row, so SET a = b, b = a swaps two columns in standard SQL and PostgreSQL. MySQL is a notable exception, because it evaluates single-table assignments from left to right.

Without a WHERE clause, UPDATE changes every row in the table. This is occasionally intended, for example when backfilling a new column, but it is the most common cause of accidental mass data changes.

Updating with data from other tables

Real updates often take new values from related tables. The SQL standard expresses this with correlated subqueries in SET, while most systems also offer a join-based syntax, which differs between dialects and is easier to read for multi-column changes.

When the source may contain several rows for one target row, the result is ambiguous: PostgreSQL and SQL Server pick one of them without warning. Aggregating or deduplicating the source first, often in a CTE, makes the outcome deterministic. When a row may or may not exist yet, an UPSERT with INSERT ... ON CONFLICT or MERGE is the better tool than a separate UPDATE and INSERT.

Locking, concurrency and performance

An UPDATE takes row-level write locks on every row it changes and holds them until the transaction ends. Other transactions that try to update the same rows wait, while readers in MVCC databases such as PostgreSQL, Oracle and MySQL InnoDB continue to see the previous committed version. Locks are also taken by the search for qualifying rows: in InnoDB, an UPDATE whose WHERE clause cannot use an index may lock far more rows than it changes.

Internally, MVCC systems usually do not overwrite the row. PostgreSQL writes a new row version and marks the old one as dead, which later requires vacuuming; InnoDB modifies the row in place and keeps the old version in undo logs. In both cases, updating many rows creates write-ahead log volume, index maintenance and replication lag proportional to the change.

Lost updates and safe concurrent changes

A classic error is the read-modify-write race: an application reads a balance of 100, computes 100 minus 30, and writes 70, while another session does the same with a different amount. One change is silently lost. Writing the arithmetic inside the UPDATE, as in SET balance = balance - 30, lets the database apply both changes correctly because each statement reads the latest committed value under its lock.

When the new value depends on more complex application logic, two patterns prevent lost updates. Pessimistic locking reads the row with SELECT ... FOR UPDATE before computing the change. Optimistic locking adds a version column and updates with WHERE id = 5 AND version = 12; if the statement reports zero affected rows, another session changed the row first and the application retries.

Common mistakes with UPDATE

• Missing or wrong WHERE clause: run the same condition as a SELECT first and compare the row count before updating.
• Ignoring the affected-row count: zero rows usually means a wrong key or a concurrent change, not success.
• Comparing with NULL using equals: WHERE deleted_at = NULL matches nothing; use IS NULL.
• Updating unchanged values: rewriting identical data still creates row versions, triggers and log traffic; add a condition that skips rows already holding the target value.
• Running large updates in autocommit without batching: long locks block users and a failure halfway is hard to resume.

Many teams also enable safe update modes in interactive tools, such as MySQL’s sql_safe_updates, which rejects UPDATE statements without a key-based WHERE or LIMIT clause.

Explained without jargon

A database table is like a large spreadsheet. The UPDATE command is the way to change values in rows that already exist, for example correcting a customer’s address or raising a price.

The command says three things: which table to change, what the new values are, and which rows the change applies to. If the last part is forgotten, every row in the table changes. That is why careful people always check the selection first.

A typical use case

An online bookstore runs a weekend sale that lowers prices in one category by 20 percent. Before the change, a database administrator runs SELECT count(*) FROM books WHERE category_id = 12 AND on_sale = false and sees 3,412 matching rows. She then runs UPDATE books SET price = round(price * 0.8, 2), on_sale = true WHERE category_id = 12 AND on_sale = false RETURNING id; inside a transaction.

The statement reports 3,412 affected rows, matching the earlier count, so she commits. Because the condition includes on_sale = false, running the script twice cannot discount a book twice. She had copied the regular prices into a regular_price column beforehand, so on Monday a similar UPDATE restores them exactly, and the stock and sales tables are never touched.