MySQL UPDATE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

UPDATE changes rows that are already there. The WHERE decides which ones, and it is the only thing standing between 1 row and the whole table. These examples use the sakila_practice database from INSERT. Run the setup block there again before you start, then open the client with mysql -u root -p sakila_practice.

How UPDATE works

UPDATE TABLE_NAME
SET COLUMN_NAME = NEW_VALUE
WHERE CONDITION;

Replace TABLE_NAME with the table, COLUMN_NAME with the column to change, NEW_VALUE with what to put in it, and CONDITION with the test that picks the rows. The WHERE works exactly as it does in a SELECT, and that is the safest way to write an UPDATE: run it as a SELECT first, look at the rows, then change SELECT ... to UPDATE ... SET ... and leave the WHERE alone.

Examples

1) Change 1 row

UPDATE member SET active = 0 WHERE member_id = 2;

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

SELECT member_id, first_name, last_name, active FROM member;

+-----------+------------+-----------+--------+
| member_id | first_name | last_name | active |
+-----------+------------+-----------+--------+
|         1 | Mary       | Smith     |      1 |
|         2 | Patricia   | Johnson   |      0 |
|         3 | Linda      | Williams  |      0 |
+-----------+------------+-----------+--------+
3 rows in set (0.00 sec)

2) Matched and Changed count different things

Run the same statement a second time.

UPDATE member SET active = 0 WHERE member_id = 2;

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

The WHERE still found the row, so Rows matched is 1. The row already held 0, so nothing was written and Changed is 0. Read the 2 numbers together: matched 0 means the WHERE found nothing, which is a different problem from changed 0, which means you asked for a value the rows already have.

3) Change several columns at once

Separate the assignments with commas, not with AND.

UPDATE member
SET last_name = 'Smith-Davis', email = 'mary.smith-davis@example.com'
WHERE member_id = 1;

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

SELECT member_id, first_name, last_name, email FROM member WHERE member_id = 1;

+-----------+------------+-------------+------------------------------+
| member_id | first_name | last_name   | email                        |
+-----------+------------+-------------+------------------------------+
|         1 | Mary       | Smith-Davis | mary.smith-davis@example.com |
+-----------+------------+-------------+------------------------------+
1 row in set (0.00 sec)

An UPDATE meets the same constraints an INSERT does

Moving a value into a UNIQUE column that another row holds fails the same way, and the row keeps what it had.

UPDATE member SET email = 'patricia.johnson@example.com' WHERE member_id = 3;

ERROR 1062 (23000): Duplicate entry 'patricia.johnson@example.com' for key 'member.uq_member_email'

Without a WHERE it changes every row

There is no confirmation step. The statement runs and every row in the table takes the new value. A session setting can refuse that shape for you. Safe update mode rejects an UPDATE or a DELETE that neither tests a key column in its WHERE nor carries a LIMIT, and it is off unless you ask for it.

SET SESSION sql_safe_updates = 1;
UPDATE member SET active = 1;

Query OK, 0 rows affected (0.00 sec)

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

Start the client with mysql -u root -p --safe-updates sakila_practice to get the guard from the first statement. That flag does more than set this 1 variable: it also caps a SELECT at 1,000 rows through sql_select_limit and bounds a join through max_join_size, so a read against a large table comes back short without saying so. ERROR 1175 means the guard stopped you. Turn it off again when the statement really is meant to touch every row.

SET SESSION sql_safe_updates = 0;

Query OK, 0 rows affected (0.00 sec)

With the guard off, the statement runs.

UPDATE member SET active = 1;

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

SELECT member_id, first_name, active FROM member;

+-----------+------------+--------+
| member_id | first_name | active |
+-----------+------------+--------+
|         1 | Mary       |      1 |
|         2 | Patricia   |      1 |
|         3 | Linda      |      1 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

Rows matched: 3 Changed: 2 because 1 member was already active. A whole-table update is sometimes exactly right. Raising every balance by 5% is 1 statement, and the new value is worked out from the old one.

UPDATE account SET balance = balance * 1.05;

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

SELECT * FROM account;

+------------+------------------+---------+
| account_id | owner            | balance |
+------------+------------------+---------+
|          1 | Mary Smith       |  525.00 |
|          2 | Patricia Johnson |  262.50 |
+------------+------------------+---------+
2 rows in set (0.00 sec)

balance = balance * 1.05 reads each row’s current value, so every row gets its own answer.

Summary

Write UPDATE table SET column = value WHERE condition to change existing rows.
Run the WHERE as a SELECT first, then change only the front of the statement.
Read Rows matched and Changed as 2 separate facts.
Separate several assignments with commas.
Expect an UPDATE to hit the same UNIQUE and NOT NULL rules an INSERT does.
Turn on safe update mode, and read ERROR 1175 as the guard doing its job.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How UPDATE works

Examples

1) Change 1 row

2) Matched and Changed count different things

3) Change several columns at once

An UPDATE meets the same constraints an INSERT does

Without a WHERE it changes every row

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How UPDATE works

​Examples

​1) Change 1 row

​2) Matched and Changed count different things

​3) Change several columns at once

​An UPDATE meets the same constraints an INSERT does

​Without a WHERE it changes every row

​Summary

​See also

How UPDATE works

Examples

1) Change 1 row

2) Matched and Changed count different things

3) Change several columns at once

An UPDATE meets the same constraints an INSERT does

Without a WHERE it changes every row

Summary

See also