SQL UPDATE Statement: Syntax, Examples, and Safe Patterns (2026)
For SQL beginners who know SELECT and WHERE, and now need to change rows that are already in a table.
On this page
UPDATE changes the values in rows that are already in a table. For example, you can change the price of one product in a products table.
UPDATE syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
UPDATEnames the table whose rows change.SETgives a column its new value. A comma separates the assignments when you change several columns.WHEREpicks the rows that change. Without WHERE, every row of the table changes.
Create a sample table of four products:
CREATE TABLE products (
id int PRIMARY KEY,
name varchar(20),
category varchar(20),
price int
);
INSERT INTO products VALUES
(1, 'Desk', 'furniture', 120),
(2, 'Chair', 'furniture', 80),
(3, 'Lamp', 'lighting', 25),
(4, 'Bulb', 'lighting', 4);
Every statement below ran on PostgreSQL 18, MySQL 9.1 and SQLite 3.50, unless its section names one engine. Run the examples in order. Each UPDATE changes the products table, and the next example starts from its rows.
UPDATE with WHERE
UPDATE with a WHERE clause changes only the rows that match the condition. For example, you can set the lamp's price to 30.
UPDATE products
SET price = 30
WHERE id = 3;
The products table after the UPDATE:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 120 |
| 2 | Chair | furniture | 80 |
| 3 | Lamp | lighting | 30 |
| 4 | Bulb | lighting | 4 |
The lamp's price went from 25 to 30. The other three rows don't match id = 3, so their values stay as they were. The id column is the primary key, which holds a different value in every row, so id = 3 matches one row at most.
SET with several columns
One SET can change several columns of the same rows. For example, you can rename the bulb and change its price in one statement.
UPDATE products
SET name = 'LED bulb', price = 6
WHERE id = 4;
The products table after the UPDATE:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 120 |
| 2 | Chair | furniture | 80 |
| 3 | Lamp | lighting | 30 |
| 4 | LED bulb | lighting | 6 |
Row 4 got both new values. The WHERE clause appears once and applies to both assignments.
SET with an expression
A new value can be an expression that reads the row's current values. For example, you can raise the price of every furniture product by 10.
UPDATE products
SET price = price + 10
WHERE category = 'furniture';
The products table after the UPDATE:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 130 |
| 2 | Chair | furniture | 90 |
| 3 | Lamp | lighting | 30 |
| 4 | LED bulb | lighting | 6 |
The desk went from 120 to 130, and the chair from 80 to 90. In price + 10, the price column gives each row's current price. Two products are furniture, so this time the WHERE clause matched two rows.
UPDATE without WHERE
Without a WHERE clause, UPDATE changes every row of the table. For example, you can raise every price by 5.
UPDATE products
SET price = price + 5;
The products table after the UPDATE:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 135 |
| 2 | Chair | furniture | 95 |
| 3 | Lamp | lighting | 35 |
| 4 | LED bulb | lighting | 11 |
All four prices went up by 5. The database can't tell this statement from an UPDATE whose WHERE you forgot, so the database runs both.
Before an UPDATE with a WHERE clause, run a SELECT with the same WHERE clause. The SELECT returns the rows that the UPDATE would change.
Practice this: Lesson 13 of the SQL tutorial runs INSERT, UPDATE and DELETE on a shop database in your browser, with checked exercises.
UPDATE in a transaction
A transaction groups statements: BEGIN starts it, and ROLLBACK undoes every change made since BEGIN. For example, you can set every price to 0 and then take the change back.
BEGIN;
UPDATE products SET price = 0;
ROLLBACK;
The products table after ROLLBACK:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 135 |
| 2 | Chair | furniture | 95 |
| 3 | Lamp | lighting | 35 |
| 4 | LED bulb | lighting | 11 |
Inside the transaction, the UPDATE set all four prices to 0. ROLLBACK discarded that change, so every price is back to its value before BEGIN. To keep a change, end the transaction with COMMIT instead.
SQL Server starts a transaction with BEGIN TRANSACTION. In SQL Server, a plain BEGIN starts a BEGIN...END block of statements.
UPDATE from another table
A subquery, a SELECT inside another statement, can read a row's new value from another table. For example, you can take new prices from a price list.
Create the price list, the new_prices table, with new prices for the desk and the chair:
CREATE TABLE new_prices (
product_id int,
price int
);
INSERT INTO new_prices VALUES
(1, 150),
(2, 99);
UPDATE products
SET price = (SELECT n.price FROM new_prices n WHERE n.product_id = products.id)
WHERE id IN (SELECT product_id FROM new_prices);
The products table after the UPDATE:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 150 |
| 2 | Chair | furniture | 99 |
| 3 | Lamp | lighting | 35 |
| 4 | LED bulb | lighting | 11 |
The desk got 150 and the chair got 99, their prices in new_prices. The subquery in SET runs once for each product, and products.id in the subquery is that product's id. In the subquery, n is a short name, an alias, for new_prices.
The lamp and the bulb have no row in new_prices, so the WHERE clause skips their rows.
UPDATE with JOIN
A join in UPDATE matches each product with a row of another table. SET can then read the columns of that other table. For example, you can write the subquery UPDATE above as a join, which each engine spells in its own way.
PostgreSQL and SQLite put the other table in FROM, and the join condition in WHERE:
UPDATE products
SET price = n.price
FROM new_prices n
WHERE n.product_id = products.id;
MySQL joins the tables between UPDATE and SET:
UPDATE products
JOIN new_prices n ON n.product_id = products.id
SET products.price = n.price;
In SQL Server, UPDATE names the alias of the target table, and FROM defines the alias:
UPDATE p
SET p.price = n.price
FROM products p
JOIN new_prices n ON n.product_id = p.id;
The products table after the join:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 150 |
| 2 | Chair | furniture | 99 |
| 3 | Lamp | lighting | 35 |
| 4 | LED bulb | lighting | 11 |
The join reads the same new prices as the subquery, so the table ends up with the same rows. The lamp and the bulb have no match in new_prices, so the join skips their rows.
The SQL Server statement wasn't run here. It follows example L of Microsoft's UPDATE documentation.
Use the subquery form when one statement has to run on several engines. Each engine spells the join differently, and the subquery runs unchanged on all four.
What goes wrong
A subquery that finds no row sets NULL
Leave out the WHERE clause of the subquery UPDATE, and the subquery also runs for the lamp and the bulb. The subquery finds no row for them in new_prices and returns NULL. Run the UPDATE in a transaction, so that ROLLBACK can undo it:
BEGIN;
UPDATE products
SET price = (SELECT n.price FROM new_prices n WHERE n.product_id = products.id);
SELECT * FROM products ORDER BY id;
ROLLBACK;
The SELECT before ROLLBACK returns:
| id | name | category | price |
|---|---|---|---|
| 1 | Desk | furniture | 150 |
| 2 | Chair | furniture | 99 |
| 3 | Lamp | lighting | NULL |
| 4 | LED bulb | lighting | NULL |
The lamp and the bulb lost their prices. Keep the WHERE clause, or use the join, which skips the rows without a match.
A subquery that returns more than one row
Add a second price for the desk to new_prices:
INSERT INTO new_prices VALUES (1, 140);
Then run the subquery UPDATE from "UPDATE from another table" again. PostgreSQL stops with the error "more than one row returned by a subquery used as an expression". MySQL stops with error 1242, "Subquery returns more than 1 row".
SQLite raises no error and uses the first row that the subquery returns. In this run, the first row kept the desk at 150.
The join forms run with no error on PostgreSQL, MySQL and SQLite. PostgreSQL and MySQL gave the desk 150, and SQLite gave it 140. The PostgreSQL documentation calls the choice of row "not readily predictable".
Give the other table one row for each row you update.
MySQL error 1175 in safe update mode
MySQL Workbench turns on safe update mode by default. In that mode, an UPDATE runs only when its WHERE clause uses a key column, such as the primary key, or when the UPDATE has a LIMIT:
SET sql_safe_updates = 1;
UPDATE products SET price = price + 5;
MySQL 9.1 stops with error 1175. The message is "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column."
An UPDATE with a condition on the primary key, such as WHERE id = 3, runs in safe update mode. For a deliberate update of every row, turn the mode off for your session with SET sql_safe_updates = 0;.
Run UPDATE in DbSchema
In the SQL Editor of DbSchema, an UPDATE that you start with the Run button stays in an open transaction until you press Commit or Rollback. Press Editor in the toolbar and choose SQL Editor. Run the SELECT with your WHERE clause, then the UPDATE, and press Commit to keep the change or Rollback to undo it. A SQLite file is the exception: there DbSchema saves each statement at once, and Rollback can't undo it.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, and run the UPDATE statements in order. The free Community Edition includes the database connection, the SQL Editor, and its Commit and Rollback buttons.
Run your next UPDATE with the schema in front of you
DbSchema reverse-engineers your database into an interactive diagram and runs SQL in an editor with explicit Commit and Rollback buttons. All three are in the free Community Edition.