MySQL UPDATE Statement: Change Rows with SET and WHERE

UPDATE changes values in rows that already exist.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

1
2

UPDATE products SET price = 90.00 WHERE name = 'Notebook';
SELECT name, price FROM products WHERE name = 'Notebook';

Output

OUTPUT

+----------+-------+
| name     | price |
+----------+-------+
| Notebook | 90.00 |
+----------+-------+
1 row in set

The syntax

Text

PLAIN

1
2
3

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

The WHERE clause decides which rows change. Without it, every row changes. Read that sentence twice. It is the most common cause of data accidents in SQL.

Several columns at once

query.sql

SQL

UPDATE products
SET price = 160.00, stock = 50
WHERE name = 'Marker Set';
SELECT name, price, stock FROM products WHERE name = 'Marker Set';

Output

OUTPUT

+------------+--------+-------+
| name       | price  | stock |
+------------+--------+-------+
| Marker Set | 160.00 |    50 |
+------------+--------+-------+
1 row in set

Columns in SET are separated by commas. Do not write AND between them: SET a = 1 AND b = 2 means “set a to the result of 1 AND b = 2“, which is nonsense.

Using the old value

The right side can use the row’s current values:

query.sql

SQL

1
2
3

UPDATE products SET stock = stock - 5 WHERE name = 'Backpack';
UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category = 'home';
SELECT name, category, price, stock FROM products WHERE name = 'Backpack' OR category = 'home' ORDER BY id;

Output

OUTPUT

+--------------+----------+--------+-------+
| name         | category | price  | stock |
+--------------+----------+--------+-------+
| Backpack     | bags     | 999.00 |    20 |
| Desk Lamp    | home     | 715.00 |    40 |
| Water Bottle | home     | 242.00 |    75 |
+--------------+----------+--------+-------+
3 rows in set

Both calculations are done by the database in one step. That matters under concurrency: two users reducing the stock at the same moment both subtract correctly (Updating Under Concurrency).

Left to right evaluation (MySQL-specific)

In MySQL, assignments in SET are evaluated left to right, and a later assignment sees the new value of an earlier one. Standard SQL does the opposite. This can surprise you:

query.sql

SQL

CREATE TABLE swap_test (a INT, b INT);
INSERT INTO swap_test VALUES (1, 2);
UPDATE swap_test SET a = b, b = a;
SELECT a, b FROM swap_test;

Output

OUTPUT

+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+
1 row in set

In most databases that would swap the values. In MySQL both become 2, because b = a sees the new a. To swap, use a temporary variable or a join. Keep each SET clause independent of the other columns.

Setting NULL and defaults

query.sql

SQL

UPDATE products SET stock = NULL WHERE name = 'Gel Pen';

Output

OUTPUT

ERROR 1048 (23000): Column 'stock' cannot be null

The column stock is NOT NULL, so MySQL refuses. You can also set a column back to its default:

query.sql

SQL

1
2

UPDATE products SET stock = DEFAULT WHERE name = 'Gel Pen';
SELECT name, stock FROM products WHERE name = 'Gel Pen';

Output

OUTPUT

+---------+-------+
| name    | stock |
+---------+-------+
| Gel Pen |     0 |
+---------+-------+
1 row in set

How many rows changed?

query.sql

SQL

UPDATE products SET category = 'bags' WHERE category = 'bags';

Output

OUTPUT

1
2

Query OK, 0 rows affected
Rows matched: 2  Changed: 0  Warnings: 0

The statement matched two rows (Rows matched: 2) but changed none (Changed: 0), because the new value equals the old one. MySQL does not write a row whose value is already correct. Your application driver may report either “matched” or “changed” as the affected rows, depending on its settings.

UPDATE with ORDER BY and LIMIT

MySQL allows both on a single-table update:

query.sql

SQL

1
2

UPDATE products SET stock = stock + 100 ORDER BY stock LIMIT 1;
SELECT name, stock FROM products ORDER BY id;

Output

OUTPUT

+---------------+-------+
| name          | stock |
+---------------+-------+
| Notebook      |   120 |
| Gel Pen       |   100 |
| Backpack      |    20 |
| Marker Set    |    50 |
| Desk Lamp     |    40 |
| Water Bottle  |    75 |
| Laptop Sleeve |    18 |
| Sticky Notes  |   210 |
+---------------+-------+
8 rows in set

The product with the lowest stock was topped up.

Common mistakes

No WHERE. Every row changes.
Wrong WHERE. WHERE category = 'Bags ' matches nothing, and you assume it worked.
Updating a key. Changing a primary key value is allowed but breaks every reference to it, unless foreign keys use ON UPDATE CASCADE. Do not change IDs.
Using = with NULL: WHERE phone = NULL matches nothing.

lightbulb

Always preview first

Write SELECT * FROM products WHERE ...;, check that these are the rows you mean, and only then replace SELECT * with UPDATE products SET ....