# ANSI Standard Syntax for UPDATE

Change existing rows with UPDATE ... SET, and always limit it with a WHERE clause you've checked with a SELECT first.

Apprentice · SQL · MySQL · Updated November 16, 2026

By Gregor Dzierzon

The ANSI standard syntax for updating data in a SQL table involves using the UPDATE statement. This statement allows you to modify existing data in one or more rows of a table. Here is the general syntax and some examples to illustrate its use:

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

`table_name`: The name of the table you want to update.

`SET`: The clause used to specify the columns to be updated and their new values.

`column1, column2, ...`: The columns you want to update.

`value1, value2, ...`: The new values for the specified columns.

`WHERE`: The clause used to specify which rows should be updated. If omitted, all rows in the table will be updated.

## Basic Examples of Using the UPDATE Statement

### Updating a Single Column

```sql
UPDATE customers
SET contact_name = 'Alfred Blauer'
WHERE customer_id = 'ALFKI';
```

This query updates the `contact_name` column to `'Alfred Blauer'` for the customer with `customer_id` `'ALFKI'`.

### Updating Multiple Columns

```sql
UPDATE products
SET unit_price = 19.99
    , units_in_stock = 25
WHERE product_id = 5;
```

This query updates both the `unit_price` and `units_in_stock` columns for the product with `product_id` 5.

## Updating Multiple Rows

```sql
UPDATE customers
SET city = 'San Francisco'
WHERE city = 'Los Angeles';
```

This query updates the city column to `'San Francisco'` for all customers currently located in `'Los Angeles'`.

## Example Without a WHERE Clause (Updating All Rows)

You should always use a `WHERE` clause when using an update statement. Otherwise, you will update all records in the table. In fact, `MySQL Workbench` prevents you from executing an update without a `WHERE` clause by default. You have to change your settings to allow the `UPDATE` statement to run without `WHERE`.

```sql
UPDATE orders
SET shipped_date = '2024-04-01';
```

This query sets the `shipped_date` column to `'2024-04-01'` for **ALL** rows in the orders table. This is almost never a scenario that you want to do in a production environment.

## Example with a Complex Condition

You can use complex conditions in the `WHERE` clause to update specific rows based on multiple criteria.

```sql
UPDATE products
SET product_name = CONCAT(product_name, ' (premium)')
    , unit_price = unit_price * 1.20
WHERE category_id = 1
    AND unit_price > 50;
```

This query adds ` (premium)` to the end of the `product_name`, and raises the `unit_price` by 20%, for every product in the Beverages category (category 1) that costs more than $50. In MySQL, `CONCAT` joins strings together; `+` only adds numbers.

## Summary

When performing an `UPDATE` statement, you should always use a `WHERE` clause. In fact, before performing the `UPDATE,` you should run a `SELECT` statement with the same `WHERE` clause so that you can verify that the correct rows will be affected.

### Example

```sql
SELECT *
FROM products
WHERE product_id = 15;
```

You can write and execute the `UPDATE` statement after verifying that the correct product was targeted with the `WHERE` clause.

```sql
UPDATE products
SET unit_price = unit_price * 1.10
WHERE product_id = 15;
```

## Do it: raise the Condiments prices by $1.50

The company that makes Northwind's sauces and spreads has raised its prices, so every product in the Condiments category now costs $1.50 more. Products in other categories keep their prices.

The `products` table (the **Tables** tab shows the SQL that makes it) has four columns:

- `product_id`: the product's number.
- `product_name`: the product's name.
- `category_id`: the product's category. Condiments is category `2`.
- `unit_price`: the price of one.

It has these rows. The three Condiments are the ones with `category_id` 2:

| product_id | product_name | category_id | unit_price | After your UPDATE |
|---|---|---|---|---|
| 1 | Chai | 1 | 18.00 | 18.00 (not Condiments: no change) |
| 2 | Chang | 1 | 19.00 | 19.00 (no change) |
| 3 | Aniseed Syrup | 2 | 10.00 | 10.00 + 1.50 = 11.50 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 22.00 | 22.00 + 1.50 = 23.50 |
| 6 | Grandma's Boysenberry Spread | 2 | 25.00 | 25.00 + 1.50 = 26.50 |
| 11 | Queso Cabrales | 4 | 21.00 | 21.00 (no change) |

After your SQL runs, the check reads `product_id`, `product_name` and `unit_price` of every product, sorted by `product_id`, and it should give exactly the prices in the last column.

**Your job:** write one `UPDATE` on `products`.

- Add 1.50 to the `unit_price` of each product whose `category_id` is `2`.
- Work the new price out from the row's current price, so the same statement works for every Condiments product.
- Every product in another category keeps its price.

Practiced live on the lesson page: write the SQL right there and check it.

## Cheat sheets

- [ANSI Standard Syntax for UPDATE](https://www.ask-codey.com/cheat-sheets/updating-data-ansi-standard-syntax-for-update-cheat-sheet)

Source: https://www.ask-codey.com/lessons/updating-data-ansi-standard-syntax-for-update
