MySQL UPDATE statement: syntax, JOINs, and common errors

Quick answer

The MySQL UPDATE statement changes existing rows. Name the table, set the new values, and filter with WHERE:

UPDATE products
SET price = 45.00
WHERE id = 1;

Leave out WHERE and every row in the table is updated. Everything else in this guide builds on that one form. Every example was run on MySQL 8.4, and the output is copied from the mysql client.

MySQL UPDATE statement syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

The examples use three small tables:

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  category VARCHAR(20),
  price DECIMAL(8,2),
  stock INT,
  updated_at DATETIME
);

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50), tier VARCHAR(10));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, status VARCHAR(20), total DECIMAL(8,2));

INSERT INTO products (name, category, price, stock) VALUES
  ('Keyboard', 'hardware', 49.00, 120),
  ('Mouse', 'hardware', 19.00, 300),
  ('Monitor', 'hardware', 189.00, 40),
  ('SQL Handbook', 'books', 29.00, 75),
  ('Desk Mat', 'accessories', 15.00, 0);

INSERT INTO customers VALUES (1, 'Ada', 'gold'), (2, 'Linus', 'standard'), (3, 'Grace', 'standard');
INSERT INTO orders VALUES
  (101, 1, 'pending', 120.00),
  (102, 2, 'pending', 40.00),
  (103, 3, 'shipped', 300.00),
  (104, 1, 'pending', 75.00);

If you need to load rows first, see MySQL INSERT.

Update one row with WHERE

UPDATE products SET price = 45.00 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Filtering on the primary key is the safest form. It touches exactly one row, and MySQL finds it through the index rather than scanning the table.

Before running an UPDATE with a broader filter, run a SELECT with the same WHERE clause. If the row count surprises you, the UPDATE would have too.

Update multiple columns

Separate assignments with commas. Expressions can refer to the current value of the column:

UPDATE products
SET price = price * 1.10, updated_at = NOW()
WHERE category = 'hardware';

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

+----+----------+--------+
| id | name     | price  |
+----+----------+--------+
|  1 | Keyboard |  49.50 |
|  2 | Mouse    |  20.90 |
|  3 | Monitor  | 207.90 |
+----+----------+--------+

The same idea runs in SQLite, so you can try it here. Change the WHERE clause and see which rows move:

Assignments run left to right

In a single-table UPDATE, MySQL evaluates assignments from left to right, and a later assignment sees the value set by an earlier one. PostgreSQL and the SQL standard do not work this way, so this catches people moving between the two:

UPDATE products SET price = price + 1, stock = price WHERE id = 4;
SELECT id, price, stock FROM products WHERE id = 4;

+----+-------+-------+
| id | price | stock |
+----+-------+-------+
|  4 | 30.00 |    30 |
+----+-------+-------+

The price started at 29.00. stock got 30, the new price, not 29. The MySQL manual says single-table assignments are "generally evaluated from left to right" and that multi-table updates give no ordering guarantee, so do not write logic that depends on the order in a JOIN update.

Rows matched vs rows changed

MySQL only counts a row as affected if its value changes. Run the first update a second time:

UPDATE products SET price = 45.00 WHERE id = 1;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The row was found, but the price was already 45.00, so nothing was written. Compare that with a WHERE that matches nothing:

UPDATE products SET stock = 50 WHERE id = 999;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Both report "0 rows affected". Only Rows matched tells you whether the row exists.

Drivers do not all report the same number. The Node.js mysql2 driver, running that same no-op update, returned this:

affectedRows: 1,
info: 'Rows matched: 1  Changed: 0  Warnings: 0',
changedRows: 0

mysql2 connects with the CLIENT_FOUND_ROWS flag, so its affectedRows is the matched count, and changedRows holds what the mysql client calls "Changed". If your code treats "0 affected rows" as "record not found", check which number your driver returns before relying on it.

Multi-table UPDATE with JOIN

To update rows based on data in another table, join the tables in the UPDATE clause and set columns on the one you want to change:

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.status = 'priority'
WHERE c.tier = 'gold' AND o.status = 'pending';

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

+-----+-------------+----------+
| id  | customer_id | status   |
+-----+-------------+----------+
| 101 |           1 | priority |
| 102 |           2 | pending  |
| 103 |           3 | shipped  |
| 104 |           1 | priority |
+-----+-------------+----------+

Ada is the only gold customer, so her two pending orders changed. MySQL puts the join before SET. PostgreSQL uses UPDATE ... FROM instead, which MySQL does not support. For a refresher on join types, see MySQL JOIN.

A multi-table UPDATE can change columns in more than one table at once:

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.status = 'review', c.tier = 'watch'
WHERE o.id = 102;

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

One row in orders and one in customers, reported together as 2.

UPDATE with ORDER BY and LIMIT

A single-table UPDATE accepts ORDER BY and LIMIT, which lets you pick which rows get changed:

UPDATE products
SET stock = stock + 10
ORDER BY stock ASC
LIMIT 2;

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

+----+--------------+-------+
| id | name         | stock |
+----+--------------+-------+
|  1 | Keyboard     |   120 |
|  2 | Mouse        |   300 |
|  3 | Monitor      |    40 |
|  4 | SQL Handbook |    40 |
|  5 | Desk Mat     |    10 |
+----+--------------+-------+

The two lowest-stock products (Desk Mat at 0, SQL Handbook at 30) each gained 10.

LIMIT is also how you break a large update into batches. Run the statement repeatedly until it reports 0 rows affected, so no single transaction holds locks on millions of rows. The same pattern works for MySQL DELETE.

Error 1221: ORDER BY or LIMIT on a multi-table UPDATE

Neither clause is allowed once a JOIN is involved:

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.status = 'archived'
WHERE c.tier = 'watch'
LIMIT 1;

ERROR 1221 (HY000): Incorrect usage of UPDATE and LIMIT

With ORDER BY instead, the message is ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY. To batch a join update, pick the target keys in a derived table that has its own ORDER BY and LIMIT, then join to it:

UPDATE orders o
JOIN (
  SELECT o2.id
  FROM orders o2
  JOIN customers c ON c.id = o2.customer_id
  WHERE c.tier = 'watch'
  ORDER BY o2.id
  LIMIT 1
) AS batch ON batch.id = o.id
SET o.status = 'archived';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Batching on a primary key range (WHERE o.id BETWEEN 1 AND 10000) works too.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

Safe updates mode: error 1175

With sql_safe_updates turned on, MySQL refuses an UPDATE that could touch every row in the table:

SET SESSION sql_safe_updates = 1;
UPDATE products SET stock = 0;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

A WHERE clause alone is not enough. It has to use an indexed column, or the statement needs a LIMIT:

UPDATE products SET stock = 0 WHERE category = 'books';
-- ERROR 1175 (HY000): You are using safe update mode ...

UPDATE products SET stock = 0 WHERE category = 'books' LIMIT 100;
-- Query OK, 1 row affected

UPDATE products SET stock = 0 WHERE id = 5;
-- Query OK, 1 row affected

category has no index, so the first statement fails. Adding LIMIT or filtering on the primary key gets through.

Safe updates mode is off by default in the server. You usually meet it because a client turned it on: MySQL Workbench enables it by default in its SQL editor (the "Safe Updates" option under Preferences, SQL Editor), and the mysql command-line client enables it with --safe-updates (or its alias, --i-am-a-dummy).

The fix depends on intent:

• If you meant to touch specific rows, add a WHERE on an indexed column. This is the right answer most of the time.
• If you meant to update every row, turn it off for the session with SET SESSION sql_safe_updates = 0;, run the statement, and turn it back on.

Error 1093: UPDATE with a subquery on the same table

A common request: cut the price of anything priced above average. The obvious query fails:

UPDATE products
SET price = price * 0.9
WHERE price > (SELECT AVG(price) FROM products);

ERROR 1093 (HY000): You can't specify target table 'products' for update in FROM clause

MySQL does not let you read from the table you are updating in a subquery, because the rows could change underneath the read. The same error appears with WHERE id IN (SELECT id FROM products WHERE ...).

Workaround 1: wrap the subquery in a derived table

Put the subquery inside another SELECT with an alias. MySQL materializes the derived table first, so the read is finished before the update starts:

UPDATE products
SET price = price * 0.9
WHERE price > (
  SELECT avg_price FROM (SELECT AVG(price) AS avg_price FROM products) AS t
);

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Only Monitor was above the average, and its price went from 207.90 to 187.11.

Workaround 2: JOIN to a derived table

For anything more involved, join to the derived table. This is the approach the MySQL manual recommends, and it reads better when you need several values from the subquery. Here the most expensive product in each category gets a label:

UPDATE products p
JOIN (
  SELECT category, MAX(price) AS max_price
  FROM products
  GROUP BY category
) m ON m.category = p.category AND p.price = m.max_price
SET p.name = CONCAT(p.name, ' (top)');

+----+--------------------+-------------+--------+
| id | name               | category    | price  |
+----+--------------------+-------------+--------+
|  1 | Keyboard           | hardware    |  49.50 |
|  2 | Mouse              | hardware    |  20.90 |
|  3 | Monitor (top)      | hardware    | 187.11 |
|  4 | SQL Handbook (top) | books       |  30.00 |
|  5 | Desk Mat (top)     | accessories |  15.00 |
+----+--------------------+-------------+--------+

UPDATE from a subquery on another table

The 1093 restriction only applies to the table being updated. A correlated subquery against a different table works directly:

UPDATE customers c
SET c.tier = 'gold'
WHERE (SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) >= 120;

Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

This is the rows-matched distinction again. Ada and Grace both qualified, but Ada was already gold, so only Grace changed.

Common mistakes

Forgetting WHERE. UPDATE products SET price = 0; changes every row and the only undo is a backup or an open transaction. Run risky updates inside START TRANSACTION, check the result with a SELECT, then COMMIT or ROLLBACK.

Reading "0 rows affected" as "not found". As shown above, a row that already holds the new value reports 0 changed. Check Rows matched, or your driver's equivalent.

Updating a column that other assignments read. Because single-table assignments run left to right, SET a = b, b = a does not swap the columns in MySQL. Both end up with the original value of b.

Unindexed filters on big tables. An UPDATE with a WHERE on an unindexed column scans the table and, in InnoDB, can lock far more rows than it changes. Index the filter column or batch on the primary key.

Quick reference

Related guides: MySQL DELETE uses the same WHERE, ORDER BY, and LIMIT rules, and MySQL JOIN covers the join types you can use in a multi-table UPDATE.