# SQL UPDATE: Modify Existing Rows With SET

- URL: https://betteranalyst.com/sql/update

SQL guide · Changing data

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

- [SQL INSERT INTO](https://betteranalyst.com/sql/insert-into)
- [SQL DELETE](https://betteranalyst.com/sql/delete)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL EXISTS](https://betteranalyst.com/sql/exists)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL INSERT INTO SELECT](https://betteranalyst.com/sql/insert-into-select)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)