> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL UPDATE > How the MySQL UPDATE statement changes rows already in a table, what Rows matched and Changed each count, and what happens when you leave out the WHERE. 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](/docs/tutorial/insert). Run the setup block there again before you start, then open the client with `mysql -u root -p sakila_practice`. ## How UPDATE works ```sql theme={null} 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 ```sql theme={null} UPDATE member SET active = 0 WHERE member_id = 2; ``` ```text theme={null} Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` ```sql theme={null} SELECT member_id, first_name, last_name, active FROM member; ``` ```text theme={null} +-----------+------------+-----------+--------+ | 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. ```sql theme={null} UPDATE member SET active = 0 WHERE member_id = 2; ``` ```text theme={null} 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`. ```sql theme={null} UPDATE member SET last_name = 'Smith-Davis', email = 'mary.smith-davis@example.com' WHERE member_id = 1; ``` ```text theme={null} Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` ```sql theme={null} SELECT member_id, first_name, last_name, email FROM member WHERE member_id = 1; ``` ```text theme={null} +-----------+------------+-------------+------------------------------+ | 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. ```sql theme={null} UPDATE member SET email = 'patricia.johnson@example.com' WHERE member_id = 3; ``` ```text theme={null} 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. ```sql theme={null} SET SESSION sql_safe_updates = 1; UPDATE member SET active = 1; ``` ```text theme={null} 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. ```sql theme={null} SET SESSION sql_safe_updates = 0; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) ``` With the guard off, the statement runs. ```sql theme={null} UPDATE member SET active = 1; ``` ```text theme={null} Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 ``` ```sql theme={null} SELECT member_id, first_name, active FROM member; ``` ```text theme={null} +-----------+------------+--------+ | 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. ```sql theme={null} UPDATE account SET balance = balance * 1.05; ``` ```text theme={null} Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 ``` ```sql theme={null} SELECT * FROM account; ``` ```text theme={null} +------------+------------------+---------+ | 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. ## See also * [WHERE](/docs/tutorial/where) — the clause that decides which rows change * [DELETE](/docs/tutorial/delete) — removing rows rather than changing them * [Upsert](/docs/tutorial/upsert) — update an existing row, or insert one if there is none * [Transactions](/docs/tutorial/transactions) — undoing an UPDATE that was wrong