MySQL online course

How to Update Field Values in MySQL with UPDATE

Learn how to safely update one or more MySQL column values with UPDATE, SET, and WHERE, including verification, LIMIT, transactions, and troubleshooting.

MySQL's UPDATE statement changes values in rows that already exist in a table. You can update one column or several columns, and you control which rows change with a WHERE condition.

In database terminology, a field is commonly called a column, while an individual record is a row. A table stores related rows and columns. INSERT adds new rows, SELECT retrieves rows, and UPDATE changes values in existing rows.

Prerequisites

• Basic knowledge of tables, columns, and rows
• A connection to MySQL and a way to execute SQL statements
• Basic use of SELECT and WHERE conditions
• An understanding of text and numeric values

Basic UPDATE syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Each part has a specific role:

Text values are normally enclosed in single quotes, such as 'Bryant'. Numeric values are typically written without quotes, such as 1991.

UPDATE testtb
SET surname = 'Bryant'
WHERE year = 1991;

This statement changes the surname value for every row whose year is 1991. The condition might match one row or many rows, so choose it carefully.

Using WHERE to select rows

WHERE determines which existing rows an UPDATE affects. It uses comparison operators to test column values.

• = means equal to
• != or <> means not equal to
• > means greater than
• < means less than
• >= means greater than or equal to
• <= means less than or equal to

UPDATE testtb
SET year = 1992
WHERE year < 1992;

The example changes every row with a year below 1992. Conditions can also be combined, for example with AND:

UPDATE testtb
SET surname = 'Bryant'
WHERE name = 'Amy' AND surname = 'Goodridge';

Whenever possible, use a primary key or another unique condition to target one intended row. A surname, name, or year may be shared by multiple people and therefore may not identify a single row.

Updating one field

Suppose testtb contains person records and Amy's surname must change from Goodridge to Bryant. First inspect the table:

SELECT * FROM testtb;

Then update the matching value:

UPDATE testtb
SET surname = 'Bryant'
WHERE surname = 'Goodridge';

This changes every row with the surname Goodridge. If only Amy should change, a more specific condition is safer:

UPDATE testtb
SET surname = 'Bryant'
WHERE name = 'Amy' AND surname = 'Goodridge';

A primary-key condition is usually better when the table has a key, because it identifies one row unambiguously:

UPDATE testtb
SET surname = 'Bryant'
WHERE person_id = 42;

After the update, verify the result with SELECT:

SELECT name, surname, year
FROM testtb
WHERE name = 'Amy';

Updating multiple fields

Use comma-separated assignments in the SET clause to change several columns in one statement. The same WHERE condition selects the rows for all assignments.

UPDATE testtb
SET surname = 'Bryant',
    year = 1991
WHERE name = 'Amy' AND surname = 'Goodridge';

This statement changes both surname and year for rows matching the condition. Do not place AND between SET assignments; separate assignments with commas.

Understanding update results

After an UPDATE, the MySQL command-line client commonly reports status information similar to:

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

• Rows matched is the number of rows satisfying the WHERE condition.
• Changed, sometimes described as affected rows, is the number of rows whose stored values actually changed.
• Warnings reports warnings produced while processing the statement.

A row can match the WHERE condition but not count as changed if the replacement value is identical to its current value. Therefore, zero changed rows can mean either that no rows matched or that matching rows already contained the requested value.

A safe update workflow

1. Inspect the table. Run a broad SELECT when you need to understand the data.
2. Preview the exact target rows. Use the same WHERE condition that will appear in UPDATE.
3. Check uniqueness. Confirm that the condition selects exactly the intended rows, preferably by primary key.
4. Make the update. Change only the required columns.
5. Verify the result. Run a follow-up SELECT using the relevant key or condition.

SELECT *
FROM testtb
WHERE surname = 'Goodridge';

UPDATE testtb
SET surname = 'Bryant'
WHERE surname = 'Goodridge';

SELECT *
FROM testtb
WHERE surname = 'Bryant';

For important changes, use a transaction when the table's storage engine and your MySQL configuration support transactions:

START TRANSACTION;

UPDATE testtb
SET surname = 'Bryant'
WHERE person_id = 42;

SELECT *
FROM testtb
WHERE person_id = 42;

COMMIT;

If verification shows an unintended result and the transaction is still open, use ROLLBACK instead of COMMIT. Keep appropriate backups and test broad production updates in a safe environment before running them.

Limiting the number of updated rows

MySQL supports LIMIT with UPDATE to cap the maximum number of rows changed:

UPDATE testtb
SET surname = 'Bryant'
WHERE surname = 'Goodridge'
LIMIT 1;

This changes no more than one matching row. LIMIT does not replace a precise WHERE clause: it only caps the result after rows have been selected. If several rows match, the particular row chosen may not be the one you intended unless you use an appropriate, tested ordering strategy where supported.

Use a unique key in the WHERE clause when the goal is to update one specific record. Treat LIMIT as an additional safety boundary, not as a substitute for identifying the correct row.

Troubleshooting UPDATE statements

Every row changed unexpectedly

The UPDATE may have omitted WHERE, or the condition may have been too broad. If the transaction is still open, use ROLLBACK. Otherwise, restore from an appropriate backup or use another controlled recovery process. Prevent this problem by previewing the rows with SELECT and using a precise primary-key condition.

More than one row changed

A non-unique condition, such as WHERE surname = 'Goodridge', may match several people. Add a primary key or additional predicates that uniquely identify the intended row.

Zero rows changed

No row may match the WHERE condition, or matching rows may already contain the requested value. Run a SELECT with the same condition and check spelling, data type, whitespace, case behavior, and the current target value.

A text value causes a syntax error

The string may not be quoted correctly or may contain an unescaped quote. Use correctly escaped string literals. In application code, prefer prepared statements and parameterized queries rather than constructing SQL by concatenating user input.

Safe-update restrictions reject the statement

A client tool may be configured to reject UPDATE statements that lack an acceptable key-based WHERE condition or LIMIT. Use a key column in the WHERE clause, add an intentional LIMIT when appropriate, or change the client's safe-update setting only after understanding the possible impact.

Exam-relevant notes

• UPDATE changes existing rows; INSERT adds rows and SELECT retrieves rows.
• SET specifies replacement values.
• WHERE selects the rows to modify.
• Leaving out WHERE updates every row in the target table.
• Text literals normally use single quotes; numeric literals normally do not.
• Matched rows satisfy the condition, while changed or affected rows actually receive different stored values.
• A primary key is the preferred way to identify one specific row.
• LIMIT caps the number of rows changed but does not make an imprecise WHERE condition precise.

Quick reference

-- Preview the target rows
SELECT * FROM testtb WHERE surname = 'Goodridge';

-- Update one column
UPDATE testtb
SET surname = 'Bryant'
WHERE surname = 'Goodridge';

-- Update multiple columns
UPDATE testtb
SET surname = 'Bryant', year = 1991
WHERE name = 'Amy' AND surname = 'Goodridge';

-- Cap the number of matching rows changed
UPDATE testtb
SET surname = 'Bryant'
WHERE surname = 'Goodridge'
LIMIT 1;

For related SQL concepts, review database terms, primary keys, the LIMIT clause, and deleting rows.