UPDATE
SQL · Modifying Data
Theory
UPDATE changes values in rows that already exist. SET lists the columns and their new values, and WHERE picks which rows to change: UPDATE employees SET salary = salary + 5000 WHERE dept = 'HR' gives every HR employee a raise and leaves everyone else alone. The new value can be a constant, an expression using the old value, or a subquery.
Without WHERE, every row in the table is updated, which is one of the most common and costly SQL mistakes. A safe habit is to run the same WHERE as a SELECT first and check which rows it returns. All SET expressions use the old row values, so SET a = b, b = a swaps two columns in standard SQL. Constraints are checked after the change, and updating a row from another table uses UPDATE ... FROM (PostgreSQL, SQL Server) or a JOIN in UPDATE (MySQL).
Why we use it
Data changes over time: salaries rise, addresses move, order statuses progress. UPDATE edits the existing rows in place so the table always reflects the current facts.
When to use it
Use UPDATE to correct or change values in existing rows. To add new rows use INSERT, and to insert-or-update in one step use MERGE or an upsert. Run it inside a transaction when changing many rows, so you can roll back.
How it works
- UPDATE employees SET ... WHERE ...
- Read the next row
- Matches the WHERE condition? Yes: Compute new values from old ones. No: Leave the row unchanged.
- Check constraints and write the new values
- Repeat for every row
- Report how many rows were updated
Syntax
UPDATE table
SET column = value
WHERE condition;Example
UPDATE employees SET salary = 70000
WHERE name = 'Asha Rao';Key points
- Always pair with WHERE or every row is updated
- Can update multiple columns in one statement
- Test the WHERE with a SELECT before running the UPDATE
- SET expressions see the old row values
- Run large updates inside a transaction so you can roll back