MySQL UPDATE Query with Example

By : Marcus Allen Marcus Allen
Hours Updated

⚡ Smart Summary

MySQL UPDATE Query modifies existing rows in a table, changing one field or several fields in a single statement. This resource explains UPDATE syntax, WHERE filtering, worked examples on the members table, and the safeguards that prevent unintended mass updates.

  • ✏️ Core Statement: UPDATE `table_name` SET `column_name` = ‘new_value’ [WHERE condition] rewrites values in every row that matches the filter.
  • ⚠️ Omitted WHERE: Without a WHERE clause, the UPDATE statement rewrites the column in every row of the table.
  • 🔢 Value Formatting: String and date values require single quotes, numeric values do not, and dates follow the ‘YYYY-MM-DD’ format.
  • 🧩 Multiple Columns: Separate each assignment with a comma to update several fields in one statement.
  • 🔍 Verification Step: Run the WHERE clause as a SELECT statement first, then repeat it after the UPDATE to confirm the new values.
  • 🛡️ Safe Update Mode: MySQL Workbench rejects any UPDATE whose WHERE clause does not filter on a key column.
Read More

What is the UPDATE Query?

The UPDATE MySQL command is used to modify rows in a table. The UPDATE query can change a single field or multiple fields at the same time. It can also be used to update a MySQL table with values taken from another table.

Why Use the UPDATE Query in MySQL?

Data rarely stays correct forever. Members change phone numbers, prices change, and orders move from pending to shipped. Deleting the old row and inserting a replacement would break the primary key and any foreign key references. The UPDATE statement edits the row in place, so identity and relationships survive the change.

The UPDATE query is the right tool whenever:

  • A value is wrong or outdated: Correct a misspelt name or refresh a contact number without touching the rest of the record.
  • A status must move forward: Flip an order from “pending” to “shipped” by rewriting a single column.
  • Several fields change together: One statement can set a new name and a new address in the same pass.
  • A bulk correction is needed: A WHERE clause that matches many rows applies the same edit to all of them at once.

Because an UPDATE without a WHERE clause rewrites every row, the filter separates a routine correction from an accidental overwrite.

MySQL Update Command Syntax

With the purpose established, the next step is the syntax. The basic form of the UPDATE query in MySQL is shown below.

UPDATE `table_name` SET `column_name` = 'new_value' [WHERE condition];

HERE:

  • UPDATE `table_name` is the command that tells MySQL to modify the data in a table.
  • SET `column_name` = ‘new_value’ gives the names and values of the fields to be affected by the UPDATE query. When setting the values, string data types must be in single quotes. Numeric values do not need quotation marks. Date values must be in single quotes and in the ‘YYYY-MM-DD’ format.
  • [WHERE condition] is optional and can be used to apply a filter that restricts the number of rows affected by the UPDATE query. The full option list appears in the MySQL UPDATE Statement manual.

Let’s now look at a practical example that updates data in the members table.

Update in MySQL Example

Suppose the members with membership numbers 1 and 2 have the following updates to be made to their records.

Membership number Updates required
1 Change contact number from 999 to 0759 253 532
2 Change the name to Janet Smith Jones and update the physical address to Melrose 123

RELATED ARTICLES

Update a Single Column

We start with membership number 1. Before making any change, let’s retrieve that record.

SELECT * FROM `members` WHERE `membership_number` = 1;

Executing the above script gives us the following results.

membership_number full_names gender date_of_birth physical_address postal_address contact_number email
1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 999 janetjones@yagoo.cm

Let’s now update the contact number using the script shown below.

UPDATE `members` SET `contact_number` = '0759 253 532' WHERE `membership_number` = 1;

Executing the above script changes the contact number from 999 to 0759 253 532 for membership number 1. Re-running the SELECT confirms the new value.

membership_number full_names gender date_of_birth physical_address postal_address contact_number email
1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 532 janetjones@yagoo.cm

Update Multiple Columns

Membership number 2 needs two fields changed at once. This is the record before the update.

membership_number full_names gender date_of_birth physical_address postal_address contact_number email
2 Smith Jones Female 23-06-1980 Park Street NULL NULL jj@fstreet.com

The following script updates both columns in one statement. Note the comma separating the two assignments.

UPDATE `members` SET `full_names` = 'Janet Smith Jones', `physical_address` = 'Melrose 123' WHERE `membership_number` = 2;

Executing the above script updates the full names for membership number 2 to Janet Smith Jones and the physical address to Melrose 123.

membership_number full_names gender date_of_birth physical_address postal_address contact_number email
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL jj@fstreet.com

💡 Tip: The “WHERE clause” is what limits the number of rows affected by the UPDATE query. Omit it and every row in the table is rewritten.

FAQs

MySQL returns a “Rows matched” and a “Rows changed” count. Rows matched shows how many rows the WHERE clause selected, while Rows changed excludes rows whose new value was identical to the old one.

Yes. Combine UPDATE with a JOIN and reference the second table in the SET clause. The join condition decides which source row supplies the new value for each target row.

Wrap it in a transaction. START TRANSACTION followed by ROLLBACK restores the old values. Once the change is committed, only a backup can recover the previous data, so test the WHERE clause with SELECT first.

Yes. AI assistants convert a described change into UPDATE syntax and suggest the WHERE clause. Give the model your table structure, then verify the filter with a SELECT statement before running the generated query.

An AI model cannot see your live rows, so a slightly wrong WHERE clause can rewrite thousands of records silently. Review every generated statement, run it on a staging copy, and keep a current backup.

Summarize this post with: