00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL UPDATE Statement

Last updated: 6 min read

The UPDATE statement changes values in existing rows: a new salary, a corrected email, a new order status. It is the "U" in CRUD. Because a mistake can change thousands of rows at once, this tutorial also covers how to update safely.

1. Syntax

syntax.sql 
UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...] [LIMIT n];
UPDATE changes matching rows onlyAn employees table with four rows. UPDATE employees SET salary = salary * 1.10 WHERE dept = 'IT' changes the two IT rows; the other rows are untouched. Without the WHERE clause every row would change.BeforenamedeptsalaryAshaIT60000BilalHR45000ChenIT72000DiaSales50000UPDATE employeesSET salary = salary * 1.10WHERE dept = 'IT';AfternamedeptsalaryAshaIT66000BilalHR45000ChenIT79200DiaSales50000rows not matching WHERE are unchanged
Figure 1: the WHERE clause decides which rows change. Without it, every row is updated.

Table: employees (6 rows)

emp_idnamedeptsalaryratingemailupdated_at
1AshaIT60000.004asha@old.exampleNULL
2BilalHR45000.003bilal@old.exampleNULL
3ChenIT72000.005chen@old.exampleNULL
4DiaSales50000.002dia@old.exampleNULL
5EshanSales53000.004eshan@old.exampleNULL
6FarahHR47000.005farah@old.exampleNULL

2. Updating One Row

query.sql 
UPDATE employees SET rating = 4 WHERE emp_id = 4;
Output 
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

MySQL reports two numbers: rows matched by the WHERE clause and rows actually changed. Running the same update again matches 1 row but changes 0, because the value is already 4, so "0 rows affected":

query.sql 
UPDATE employees SET rating = 4 WHERE emp_id = 4;
Output 
Query OK, 0 rows affected
Rows matched: 1  Changed: 0  Warnings: 0

3. Updating Several Columns and Using Calculations

multi-column.sql 
UPDATE employees
SET salary = salary * 1.10,
    rating = LEAST(rating + 1, 5)
WHERE dept = 'IT';

SELECT name, dept, salary, rating FROM employees WHERE dept = 'IT';

Result: 2 rows

namedeptsalaryrating
AshaIT66000.005
ChenIT79200.005
MySQL evaluates single-table assignments left to right, so a later expression sees an earlier column's new value. SET a = a + 1, b = a gives b the incremented value. Many other databases use the old value instead.

4. Updating Text with Functions

replace-domain.sql 
UPDATE employees
SET email = REPLACE(email, '@old.example', '@corp.example');
SELECT name, email FROM employees ORDER BY emp_id LIMIT 3;

Result: 3 rows

nameemail
Ashaasha@corp.example
Bilalbilal@corp.example
Chenchen@corp.example

5. Different Values per Row with CASE

case.sql 
UPDATE employees
SET salary = CASE
               WHEN rating = 5 THEN salary * 1.15
               WHEN rating = 4 THEN salary * 1.08
               ELSE salary * 1.03
             END
WHERE dept IN ('HR', 'Sales');

SELECT name, dept, rating, salary FROM employees WHERE dept IN ('HR', 'Sales') ORDER BY emp_id;

Result: 4 rows

namedeptratingsalary
BilalHR346350.00
DiaSales454000.00
EshanSales457240.00
FarahHR554050.00

6. UPDATE with a JOIN

Use another table's values in the update. Give each employee a bonus percentage from departments:

update-join.sql 
ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2) DEFAULT 0;

UPDATE employees e
JOIN departments d ON d.dept = e.dept
SET e.bonus = ROUND(e.salary * d.bonus_pct / 100, 2);

SELECT name, dept, salary, bonus FROM employees ORDER BY emp_id;

Result: 6 rows

namedeptsalarybonus
AshaIT66000.005280.00
BilalHR46350.002317.50
ChenIT79200.006336.00
DiaSales54000.005400.00
EshanSales57240.005724.00
FarahHR54050.002702.50

7. UPDATE with a Sub-query

subquery.sql 
UPDATE employees
SET rating = rating + 1
WHERE rating < 5
  AND salary < (SELECT avg_sal FROM (SELECT AVG(salary) AS avg_sal FROM employees) AS t);

SELECT name, salary, rating FROM employees ORDER BY emp_id;

Result: 6 rows

namesalaryrating
Asha66000.005
Bilal46350.004
Chen79200.005
Dia54000.005
Eshan57240.005
Farah54050.005

MySQL doesn't let a sub-query read the same table you are updating directly; wrapping it in a derived table (AS t) works around this:

query.sql 
UPDATE employees SET rating = 1 WHERE salary = (SELECT MIN(salary) FROM employees);
Output 
ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause

8. ORDER BY and LIMIT

Update only the first N rows in a chosen order, for example the two lowest-paid employees:

limit.sql 
UPDATE employees SET salary = salary + 2000 ORDER BY salary ASC LIMIT 2;
SELECT name, salary FROM employees ORDER BY salary LIMIT 3;

Result: 3 rows

namesalary
Bilal48350.00
Farah54050.00
Dia56000.00

This also helps update huge tables in small batches (… WHERE processed = 0 LIMIT 5000, repeated) without long locks.

9. Updating Safely

The classic accident is forgetting the WHERE clause. Protect yourself:

  • Run the WHERE clause as a SELECT first and check the rows.
  • Wrap risky updates in a transaction and ROLLBACK if the count is wrong.
  • Turn on safe-update mode: it rejects UPDATE/DELETE without a key in WHERE or a LIMIT. The mysql client enables it with --safe-updates; MySQL Workbench has it on by default.
safe-updates.sql 
SET sql_safe_updates = 1;
UPDATE employees SET dept = 'IT';     -- no WHERE: rejected
Output 
Query OK, 0 rows affected
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
rollback.sql 
SET sql_safe_updates = 0;
START TRANSACTION;
UPDATE employees SET dept = 'IT';     -- oops
SELECT dept, COUNT(*) FROM employees GROUP BY dept;
ROLLBACK;
SELECT dept, COUNT(*) AS staff FROM employees GROUP BY dept ORDER BY dept;

Inside the transaction

deptCOUNT(*)
IT6

After ROLLBACK

deptstaff
HR2
IT2
Sales2

10. Updating from Java

UpdateSalary.java 
String sql = "UPDATE employees SET salary = salary * ? WHERE dept = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setBigDecimal(1, new BigDecimal("1.05"));
    ps.setString(2, "HR");
    int changed = ps.executeUpdate();          // number of rows affected
    System.out.println(changed + " employees updated");
}
By default Connector/J's executeUpdate() returns the number of rows matched by the WHERE clause, even if some already had the new value. Add useAffectedRows=true to the JDBC URL to get the number of rows actually changed, as the mysql client reports.

11. Practice Exercises

Exercise 1: Give everyone in Sales with rating 4 or more a 5% raise.
solution.sql 
UPDATE employees SET salary = salary * 1.05 WHERE dept = 'Sales' AND rating >= 4;
SELECT name, salary FROM employees WHERE dept = 'Sales';

Result: 2 rows

namesalary
Dia58800.00
Eshan60102.00
Exercise 2: Set each employee's rating to 3 if it's below 3, using one statement with no CASE.
solution.sql 
UPDATE employees SET rating = 3 WHERE rating < 3;
SELECT name, rating FROM employees ORDER BY emp_id;

Result: 6 rows

namerating
Asha5
Bilal4
Chen5
Dia5
Eshan5
Farah5

12. Summary

  • UPDATE t SET col = value WHERE …; changes matching rows; without WHERE, all rows change.
  • Update several columns at once, use expressions, functions and CASE.
  • Join other tables or use (derived) sub-queries for values from elsewhere.
  • ORDER BY + LIMIT updates a limited number of rows.
  • Check with SELECT first, use transactions, and consider safe-update mode.