VMware ESXi and vSphere Cluster Management

Update Field Values in MySQL with the UPDATE Statement

Learn how to safely change one or more MySQL table columns with UPDATE, SET, WHERE, data types, verification, LIMIT, and transaction precautions.

The MySQL UPDATE statement changes data that is already stored in existing table rows. Use it when a record needs correction or modification.

An update is different from other common SQL operations:

• UPDATE modifies values in existing rows.
• INSERT adds new rows.
• SELECT reads rows without changing them.
• DELETE removes existing rows.

In modern SQL terminology, a table has columns and rows. A column is a named data attribute, while a row is one record. The term field is also commonly used informally to mean a column.

Basic UPDATE syntax

UPDATE table_name
SET column_name = value
WHERE condition;

The statement has these parts:

The general form for more than one column is:

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE column_name operator value;

Each assignment has the form column = value or column = expression. Separate multiple assignments with commas, not with another SET keyword.

Update a single column

Suppose a table named testtb contains name, surname, and year columns. First inspect the current data:

SELECT * FROM testtb;

To change Amy's surname from Goodridge to Bryant, use:

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

The SET clause assigns the new surname. The WHERE condition restricts the update to rows whose name is Amy and whose current surname is Goodridge.

Select the rows to update with WHERE

A condition is a logical test that determines whether a row matches. The WHERE clause applies that test to each row. Only matching rows are eligible for modification.

The equality operator is =:

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

This may update more than one row if several rows have the name Amy. A value that looks unique is not necessarily unique in the table.

Conditions can use other operators and logical keywords, such as:

• <, >, <=, and >= for numeric or comparable values.
• <> or != for “not equal.”
• AND when every combined condition must match.
• OR when at least one combined condition can match.

For example, combining name and the original surname makes the target narrower:

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

For one intended record, filtering by a primary key or another unique identifier is safer:

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

Update multiple columns

Place several assignments in one SET clause and separate them with commas:

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

This changes both surname and year for every row matching the WHERE condition. The order of assignments does not replace the need for a precise condition.

Strings, numbers, and NULL

• Write string literals in single quotes, such as 'Bryant' or 'Amy'.
• Numeric values normally do not need quotes, such as 1992 or 42.
• Use NULL without quotes when intentionally setting a nullable column to the SQL NULL value.

UPDATE testtb
SET surname = NULL
WHERE person_id = 42;

NULL means missing or unknown, not an ordinary text value. Therefore, use IS NULL when testing for it:

SELECT *
FROM testtb
WHERE surname IS NULL;

Quoting a value incorrectly can cause a syntax error or store a value with an unintended type. When text contains an apostrophe, use the escaping rules supported by your SQL client and MySQL configuration, or use parameterized queries in application code.

Verify an update before and after execution

A safe workflow uses SELECT with the intended condition before running UPDATE:

SELECT *
FROM testtb
WHERE surname = 'Goodridge';

Review the returned rows. If the result includes more records than intended, improve the condition before changing any data.

After the update, query the target again:

SELECT *
FROM testtb
WHERE name = 'Amy';

After changing Amy's surname, the relevant data could look like this:

MySQL commonly reports information such as:

• Rows matched: rows that satisfied the WHERE condition.
• Rows changed: matched rows whose stored value actually changed.
• Warnings: notices about issues such as value conversion, truncation, or other nonfatal problems.

Rows matched can be greater than rows changed. For example, assigning 'Bryant' to a row that already contains 'Bryant' may match the row but produce no data change.

Safety considerations

UPDATE testtb
SET surname = 'Bryant';

The statement above does not mean “update one surname.” It assigns 'Bryant' to every row in testtb.

Use these precautions:

1. Run a SELECT using the exact intended WHERE condition.
2. Check how many rows are returned and confirm their values.
3. Use a primary key or another unique identifier when changing one specific record.
4. Use a transaction for consequential changes when the storage engine and workflow support it.
5. Keep a suitable backup before large or irreversible changes.

A transaction lets you inspect the result before permanently committing it:

START TRANSACTION;

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

SELECT *
FROM testtb
WHERE person_id = 42;

-- Keep the change:
COMMIT;

-- Or undo it instead:
-- ROLLBACK;

Do not issue both COMMIT and ROLLBACK for the same decision. Choose the one that matches the verification result.

Limit the number of affected rows

MySQL supports an optional LIMIT clause on an UPDATE:

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

This caps the update at one matched row. However, LIMIT is not a replacement for a correct WHERE condition. If several rows match, the selected row may not be the one you intended unless the target is uniquely identified or the selection strategy is deterministic.

For a specific record, prefer a unique condition:

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

When selecting a limited set based on an ordering rule, first identify the intended keys with an ordered SELECT, then update those keys. This makes the target explicit rather than relying on an unspecified row order.

Troubleshooting UPDATE statements

Every row changed unexpectedly

The likely cause is an omitted WHERE clause. If the operation is inside an active transaction, use ROLLBACK. Otherwise, restore the affected values from a reliable backup or another recovery source. Always preview the target rows with SELECT first.

More than one row was updated

The condition probably used a non-unique value, such as a shared surname. Use a primary key or combine conditions to identify the intended row precisely.

No rows were changed

No row may match the condition, or the assigned value may already be stored. Run a SELECT with the same condition and compare the result with MySQL's rows matched and rows changed information.

Text assignment causes a syntax error

Check that the string literal uses single quotes:

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

Also check embedded apostrophes and use the escaping or parameter-binding method provided by your SQL tool.

Exam-relevant points

• UPDATE modifies existing rows; it does not insert a new record.
• SET defines the new column values.
• WHERE determines which rows are eligible for modification.
• Omitting WHERE updates every row.
• Multiple assignments in SET are separated by commas.
• Strings normally use single quotes; numbers normally do not require quotes; NULL is unquoted.
• LIMIT can cap affected rows but cannot make an imprecise condition safe or deterministic.