Modification
The UPDATE Statement
The UPDATE statement modifies existing rows in a table.
Overview
The UPDATE statement modifies existing rows in a table. It is a core Data Manipulation Language (DML) command, standardized in SQL-86 and supported by virtually every relational database system. Unlike INSERT, which adds new rows, UPDATE changes values in columns of rows that already exist. The statement operates on a set of rows determined by an optional WHERE clause; without a WHERE clause, all rows in the target table are updated. UPDATE can modify one or more columns in a single statement, and the new values can be constants, expressions, or results from subqueries. Because it alters data in place, UPDATE is transactional in most systems: changes can be rolled back if wrapped in a transaction. The statement is often used in conjunction with SELECT to verify which rows will be affected, and with JOIN (in vendor-specific syntax) to update based on related tables. Understanding UPDATE is essential for maintaining data accuracy and implementing business logic that reacts to changing conditions.
Syntax and usage
The standard SQL syntax for UPDATE is: UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition]. The SET clause lists assignments, each specifying a column and its new value. Values can be literals (e.g., 'active'), expressions (e.g., salary * 1.1), or scalar subqueries. The WHERE clause filters which rows to update; omitting it updates every row. Multiple columns are separated by commas. Standard SQL also allows a cursor-based update with WHERE CURRENT OF cursor_name, but this is less common. Vendor extensions include UPDATE ... FROM (PostgreSQL, SQL Server) or UPDATE ... JOIN (MySQL) to update using data from another table. Some systems support RETURNING (PostgreSQL, Oracle) to return modified rows. The statement can also include an optional WHERE clause with subqueries, and in some databases, LIMIT or TOP to restrict the number of rows updated. Always verify the WHERE clause with a SELECT before executing an UPDATE to avoid unintended changes.
How it works
When an UPDATE statement is executed, the database engine first identifies the target table and parses the SET and WHERE clauses. It then evaluates the WHERE condition against each row to determine the set of rows to modify. For each qualifying row, the expressions in the SET clause are evaluated, and the new values are written to the corresponding columns. If a column is not listed in SET, its value remains unchanged. The update is typically atomic per row, but the entire statement may be transactional: either all rows are updated or none, depending on the transaction boundaries. Most systems acquire locks on the affected rows (and sometimes pages or tables) to ensure isolation. Constraints such as primary key, foreign key, unique, check, and not-null are enforced after the update; violations cause the statement to fail and roll back. Triggers may fire before or after the update, depending on the database. The order of row updates is not guaranteed unless an ORDER BY is specified in vendor extensions. After execution, the number of affected rows is usually reported, which helps confirm the operation's scope.
Examples
A simple update changes a single column for a specific row: UPDATE employees SET salary = 50000 WHERE employee_id = 101;. This increases the salary of employee 101 to 50000. To update multiple columns, list them in the SET clause: UPDATE products SET price = price * 1.1, last_updated = CURRENT_DATE WHERE category = 'Electronics';. This raises prices by 10% and sets the last_updated date for all electronics products. An update based on a subquery can set values from another table: UPDATE orders SET status = 'Shipped' WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');. This marks orders as shipped for all US customers. In PostgreSQL, you can use UPDATE ... FROM to update with a join: UPDATE orders SET total = o.total + t.shipping FROM totals t WHERE orders.order_id = t.order_id;. MySQL uses UPDATE orders JOIN totals ON orders.order_id = totals.order_id SET orders.total = totals.total + totals.shipping;. Always test the WHERE clause with a SELECT first.
Common pitfalls
The most frequent mistake is omitting or incorrectly specifying the WHERE clause, which updates all rows in the table. For example, UPDATE employees SET salary = 0; would reset every salary. Another pitfall is using a subquery that returns multiple rows in a scalar context, causing an error. In some databases, updating a column with a subquery that references the same table can lead to unexpected results or errors. Not considering constraints can cause failures: updating a primary key to a duplicate value violates uniqueness. Triggers might have side effects, such as cascading updates or logging, that are not obvious. Transaction handling is crucial: without an explicit transaction, each statement auto-commits, making rollback impossible. In concurrent environments, lost updates can occur if isolation levels are not set appropriately. Finally, vendor-specific syntax like UPDATE ... FROM may not be portable; relying on it reduces cross-database compatibility.
Best practices
Always use a WHERE clause unless you intentionally want to update every row. Before executing an UPDATE, run a SELECT with the same WHERE condition to preview affected rows. Wrap updates in a transaction so you can roll back if something goes wrong. Use explicit column lists in SET; avoid UPDATE table SET * (not standard). Consider adding a RETURNING clause (if supported) to capture changed values. For large updates, batch them to reduce lock contention and transaction log growth. Test on a development copy first. Keep statistics up to date to help the optimizer choose efficient plans. Avoid updating primary key columns unless necessary. Use parameterized queries to prevent SQL injection. Document any triggers that fire on update. When updating based on another table, prefer standard SQL subqueries for portability, or use vendor extensions with awareness of their limitations. Regularly review and optimize indexes on columns used in WHERE clauses to speed up updates.
Frequently asked questions
Q: What happens if I omit the WHERE clause? A: All rows in the table are updated. This is rarely desired and can be catastrophic; always include a WHERE clause unless you intend to modify every row. Q: Can I update multiple tables in one statement? A: Standard SQL does not allow updating multiple tables directly. Some databases support multi-table updates via vendor-specific syntax (e.g., MySQL's UPDATE t1, t2 SET ... or PostgreSQL's UPDATE ... FROM), but these are not portable. Q: How do I update a column with values from another table? A: Use a subquery in the SET clause: UPDATE t1 SET col = (SELECT col FROM t2 WHERE t2.id = t1.id);. Alternatively, use a vendor-specific join syntax. Ensure the subquery returns at most one row per target row to avoid errors.