MySQL UPDATE Statement

The UPDATE statement is used to modify existing data in a table. It is the U in CRUD — Update. You use it whenever a value in the database needs to change — correcting a typo, updating a price, changing a user's email address, or marking an order as shipped.

---

Syntax

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

• SET specifies which columns to change and what their new values should be.
• WHERE filters which rows to update. Without it, every row in the table is updated.

---

Sample Table

All examples on this page use the following students table:

id	name	city	age
1	Rahim Uddin	Dhaka	22
2	Sara Begum	Chittagong	20
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21
5	Rafi Hossain	Rajshahi	24

---

Updating a Single Row

The most common pattern is targeting a specific row by its primary key. This guarantees only one row is affected:

UPDATE students
SET city = 'Khulna'
WHERE id = 2;

Sara Begum's city is now changed from Chittagong to Khulna. MySQL confirms:

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

---

Updating Multiple Columns at Once

You can update as many columns as you need in one statement by separating them with commas in the SET clause:

UPDATE students
SET city = 'Barishal', age = 25
WHERE id = 3;

Karim Ali now has city = 'Barishal' and age = 25.

---

Updating Multiple Rows

If the WHERE condition matches more than one row, all matching rows are updated. The example below gives every student from Dhaka a new city:

UPDATE students
SET city = 'Narayanganj'
WHERE city = 'Dhaka';

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

Both Rahim Uddin (id=1) and Nila Akter (id=4) are updated in one operation.

---

Using Expressions in SET

The new value does not have to be a fixed number or string — you can use an expression based on the current value of the column:

-- Increase every student's age by 1
UPDATE students
SET age = age + 1;

-- Apply a 10% discount to all products priced above 500
UPDATE products
SET price = price * 0.90
WHERE price > 500;

---

Verify Before You Update

Before running an UPDATE, it is good practice to run a SELECT with the same WHERE condition first to confirm exactly which rows will be affected:

-- Step 1: Check which rows will be affected
SELECT * FROM students WHERE city = 'Dhaka';

-- Step 2: Once confirmed, run the update
UPDATE students
SET city = 'Narayanganj'
WHERE city = 'Dhaka';

---

The Danger of UPDATE Without WHERE

If you omit the WHERE clause, MySQL updates every single row in the table — there is no confirmation prompt:

-- DANGEROUS: This updates ALL students' city
UPDATE students
SET city = 'Dhaka';

---

Key Points to Remember

• UPDATE modifies existing rows — it never adds new rows or removes them.
• Always use a WHERE clause to target specific rows — omitting it updates the entire table.
• Target rows by primary key (WHERE id = n) when you want to update exactly one row.
• You can update multiple columns in one statement using a comma-separated SET list.
• Run a SELECT with the same WHERE condition first to preview what will be changed.
• Expressions like price = price * 0.90 are valid in the SET clause.

---

Common Mistakes to Avoid

---

Try It: A Safe Two-Step Update

A second example — previewing exactly which rows will change before committing to the update:

-- Step 1: preview
SELECT id, name, age FROM students WHERE age < 20;

-- Step 2: apply, once the preview looks correct
UPDATE students
SET age = age + 1
WHERE age < 20;

---

Frequently Asked Questions