SQL Update

The UPDATE statement changes the values of existing rows in a table — it does not add new rows or remove any. It’s one of the four fundamental data-modification statements in SQL, alongside INSERT, DELETE, and SELECT, and it’s the tool you reach for whenever data needs to change in place: giving employees a raise, correcting a typo in a customer’s address, marking an order as shipped, or recalculating a balance. Used correctly it’s simple and precise; used without care — especially without a WHERE clause — it can silently rewrite an entire table in one statement.

Overview: How UPDATE Works

An UPDATE statement always targets exactly one table (some databases support updating through a view or joining additional tables, but the row that actually changes belongs to one target table). Conceptually, the database engine processes it in three phases:

First, it identifies the candidate rows — every row in the target table, filtered down by the WHERE clause exactly the way a SELECT ... WHERE would filter rows. If you omit WHERE, every single row is a candidate. Second, for each candidate row, it evaluates the expressions in the SET clause using that row’s current values — so SET salary = salary * 1.1 reads the row’s existing salary before overwriting it, and every column reference on the right-hand side of SET refers to the pre-update value, not a value already changed earlier in the same statement. Third, it validates any constraints (NOT NULL, CHECK, UNIQUE, foreign keys) against the new values and, if everything passes, writes the new row values.

Crucially, an UPDATE statement is atomic: either every qualifying row is updated successfully, or (if a constraint violation or error occurs partway through) the whole statement is rolled back and nothing changes. It never leaves the table half-updated. The engine also reports how many rows were affected — that count is metadata returned by the database driver, not a result set; UPDATE itself does not return rows (unless you use a database-specific RETURNING clause, supported by SQLite 3.35+, PostgreSQL, and others, to get the new row values back immediately).

Because WHERE in an UPDATE works exactly like WHERE in a SELECT, anything you can filter with a SELECT — comparisons, AND/OR, IN, BETWEEN, LIKE, subqueries — you can use to target the exact rows you mean to change. A good habit, covered under Best Practices below, is to write and test that WHERE clause as a SELECT first.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;
Part Meaning
UPDATE table_name The single table whose rows will be modified.
SET column = value One or more comma-separated assignments. value can be a literal, an arithmetic expression, a CASE expression, or a subquery that returns a single value.
WHERE condition Optional, but almost always required in practice. Restricts the statement to rows matching the condition. Omitting it updates every row in the table.

Note: standard SQL’s UPDATE does not support ORDER BY or LIMIT (MySQL offers these as a non-standard extension). If you need to update only “the first N matching rows,” you generally need a subquery on a unique key instead.

Examples

Example 1: Updating a single row by primary key

Suppose a products_inventory table starts with these rows:

id product_name quantity price
1 Wireless Mouse 50 19.99
2 USB-C Cable 120 9.99
3 Mechanical Keyboard 15 79.99

A shipment reduces the Wireless Mouse stock to 45 units:

UPDATE products_inventory
SET quantity = 45
WHERE id = 1;

Result (querying the table afterward):

id product_name quantity price
1 Wireless Mouse 45 19.99
2 USB-C Cable 120 9.99
3 Mechanical Keyboard 15 79.99

The WHERE id = 1 clause matches exactly one row, so only that row’s quantity changes; every other column and every other row is untouched.

Example 2: Updating many rows at once with an expression

You can update every row that matches a condition in one statement, and the new value can be computed from the old one. Giving every Engineering employee a 10% raise:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

This does not return any rows — UPDATE never does. It scans the employees table, and for every row where department equals 'Engineering' it multiplies that row’s own salary by 1.10 and writes the result back. Employees in Sales or Marketing are never touched because they don’t match the WHERE clause. If three rows match, three rows change; the database driver reports “3 rows affected.”

Example 3: Updating with a CASE expression

SET values don’t have to be simple literals — a CASE expression lets one statement assign different values to different rows based on their own data. Given an accounts table:

id name balance status
1 Alice 250.00 active
2 Bob 0.00 active
3 Carla -50.00 active
4 Dan 1200.00 active 
UPDATE accounts
SET status = CASE
    WHEN balance < 0 THEN 'overdrawn'
    WHEN balance = 0 THEN 'empty'
    ELSE 'active'
END;

Result:

id name balance status
1 Alice 250.00 active
2 Bob 0.00 empty
3 Carla -50.00 overdrawn
4 Dan 1200.00 active

There is no WHERE clause here, so every row is a candidate — but the CASE expression re-evaluates per row, so each row still ends up with the status that matches its own balance rather than one value being applied to all four.

How It Works Step by Step

It helps to think of UPDATE as running through the same logical stages as a SELECT, plus a final write phase:

  • FROM (implicit): the target table named after UPDATE is opened.
  • WHERE: the engine walks the table (using an index if one exists on the filtered column, otherwise a full table scan) and determines which rows qualify — identical logic to filtering in a SELECT.
  • SET evaluation: for each qualifying row, every expression on the right of = in the SET clause is computed using that row’s original, pre-update values.
  • Constraint checking: the proposed new row is validated against NOT NULL, CHECK, UNIQUE, and foreign key constraints.
  • Write: if valid, the row is overwritten in place. If any qualifying row fails validation, the entire statement is rolled back — no partial updates.
  • Row count reported: the driver/API returns how many rows were changed (in SQLite, available via a function like changes(); in most client libraries, an “affected rows” property).

An important nuance: because SET expressions read the row’s original values, SET a = b, b = a genuinely swaps two columns’ values — it doesn’t set both to whatever b originally was.

Common Mistakes

1. Forgetting the WHERE clause

The intent was to change theme preference for one user. The WHERE clause was dropped by accident:

UPDATE user_settings
SET theme = 'dark';

This executes without any error — that’s what makes it dangerous. It sets every row’s theme to 'dark', silently overwriting other users’ preferences. The fix is to always scope the statement:

UPDATE user_settings
SET theme = 'dark'
WHERE user_id = 1;

Now only the row for user_id = 1 changes, and every other user’s stored preference is left exactly as it was.

2. Misspelling or referencing a column that doesn’t exist

UPDATE employees
SET slaray = salary * 1.05
WHERE department = 'Sales';

This fails outright with an error like no such column: slaray — a typo in the SET target column. Nothing is updated (the statement never runs at all). The corrected version simply fixes the column name:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales';

3. Using an aggregate function directly in SET

UPDATE employees
SET salary = AVG(salary);

This also fails — aggregate functions like AVG(), SUM(), and COUNT() can’t be evaluated per-row the way SET expressions need; the engine rejects it with a “misuse of aggregate function” error. To assign an aggregate value, wrap it in a scalar subquery instead:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees);

The subquery is evaluated once against the table’s original data, and every row is set to that single average value.

Best Practices

  • Before running an UPDATE, run the same condition as a SELECT * FROM table WHERE ... first, to see exactly which rows will be affected.
  • Never run UPDATE without a WHERE clause unless you deliberately intend to change every row.
  • Wrap risky or bulk updates in an explicit transaction (BEGIN / COMMIT) so you can ROLLBACK if the affected-row count looks wrong.
  • Reference columns by name in SET, and never assume column order matters — UPDATE has no positional form the way some INSERT shortcuts do.
  • Be cautious updating primary key or foreign key columns — changing them can silently break relationships to other tables unless ON UPDATE CASCADE is configured.
  • Prefer set-based updates (one statement covering many rows via WHERE) over looping row-by-row in application code — it’s dramatically faster and atomic.
  • Check the “rows affected” count your driver returns after every meaningful update — an unexpectedly high or low number is often the first sign of a bad WHERE clause.

Practice Exercises

  • Given the employees table, write an UPDATE statement that gives every employee in the 'Marketing' department a 7% raise.
  • Given the customers table, write an UPDATE statement that changes the country value 'UK' to 'United Kingdom' wherever it appears.
  • Using the products table, write an UPDATE with a CASE expression that sets a new price_tier value to 'premium' for products priced above 500, and 'standard' for everything else.

Summary

  • UPDATE modifies existing rows in place; it never adds or removes rows.
  • The WHERE clause is filtered exactly like in a SELECT — and if it’s omitted, every row in the table is updated.
  • SET expressions are evaluated using each row’s original values, so expressions like salary * 1.1 or column swaps behave predictably.
  • UPDATE is atomic: it either fully succeeds for all qualifying rows or fully rolls back.
  • Aggregate functions can’t be used directly in SET — use a scalar subquery instead.
  • Always test your WHERE clause with a SELECT first, and consider wrapping bulk updates in a transaction.