SQL guide · Changing data

SQL UPDATE: Modify Existing Rows With SET

The UPDATE statement changes the values in rows that already exist in a table. You name the table, use SET to list the columns and their new values, and add a WHERE clause to pick which rows are affected. The new values can be literals, expressions that use the current column values, or the result of a subquery.

The WHERE clause is what keeps an update safe. Without it, every row in the table is updated, which is rarely what you want. A good habit is to write the matching SELECT first, check that it returns exactly the rows you expect, and then convert it into an UPDATE. For important changes, run the statement inside a transaction so that you can roll it back if the number of affected rows looks wrong.

An update fires any triggers on the table and must satisfy all constraints. Changing a primary key that other tables reference may fail or cascade depending on the foreign key rules, and setting a NOT NULL column to NULL is rejected. Most clients report the number of rows changed, which is worth checking every time.

Basic syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Each assignment in SET is separated by a comma, and the right side can refer to the current row, for example price = price * 1.10. Rows that do not satisfy WHERE are untouched. Omitting WHERE updates every row.

SQL UPDATE examples

Update one row

This changes the email address of a single customer, identified by the primary key. Filtering on a unique key is the safest way to be sure only one row changes.

UPDATE customers
SET email = 'dana.ortiz@newmail.com'
WHERE customer_id = 42;

Update several columns with an expression

Two columns are set in one statement. The price is raised by 10 percent using its current value, and a timestamp records the change, for all products in one category.

UPDATE products
SET unit_price = unit_price * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE category = 'Electronics';

Update using a subquery

The new value can come from another table. This flags customers as active when they have placed an order in 2026, using EXISTS to test for a related row.

UPDATE customers
SET is_active = TRUE
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = customers.customer_id
    AND o.order_date >= '2026-01-01'
);

Update from a joined table

Copying a value from another table is a common cleanup task. The standard way is a correlated subquery, which works in every database and avoids joined-update syntax differences.

UPDATE order_items
SET unit_price = (
  SELECT p.unit_price
  FROM products AS p
  WHERE p.product_id = order_items.product_id
)
WHERE unit_price IS NULL;

Where SQL dialects differ

PostgreSQL: Supports UPDATE ... FROM other_table WHERE ... for joined updates and RETURNING to see the changed rows. There is no LIMIT on UPDATE.

MySQL: Allows UPDATE a JOIN b ON ... SET a.col = b.col for joined updates, and ORDER BY ... LIMIT n on single-table updates. Safe update mode in some clients rejects updates without a key in WHERE.

SQL Server: Uses UPDATE t SET ... FROM t INNER JOIN other ON ... for joined updates, and UPDATE TOP (n) to limit the rows changed. OUTPUT inserted.*, deleted.* returns old and new values.

SQLite: Supports UPDATE ... FROM since version 3.33.0 (2020). LIMIT on UPDATE is only available when SQLite is compiled with a special option, so use a subquery on the key instead.

Oracle: Has no joined UPDATE syntax. Use a correlated subquery, MERGE, or an updatable inline view. RETURNING ... INTO fetches the new values into variables.

Common mistakes

  • Forgetting the WHERE clause and updating every row in the table. Test with a SELECT first and use a transaction.
  • Writing a WHERE condition that is too broad, such as matching on a name instead of a unique id, so more rows change than intended.
  • Using = to test NULL. WHERE col = NULL never matches, so use WHERE col IS NULL.
  • Using a subquery that returns more than one row for a single assignment. This raises an error, or in some databases picks an arbitrary value.
  • Using a joined update where several joined rows match one target row. The result is unpredictable because only one of the matches is applied.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator