What is the MySQL UPDATE Statement?
The UPDATE
statement is used to modify the existing records in a table. First, specify the name of the table that you want to update data after the UPDATE keyword. Second, specify which column you want to update and the new value in the SET clause.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
In this syntax:
- First, specify the name of the table that you want to update data after the
UPDATE
keyword. - Second, specify which column you want to update and the new value in the
SET
clause. To update values in multiple columns, you use a list of comma-separated assignments by supplying a value in each column’s assignment in the form of a literal value, an expression, or a subquery. - Third, specify which rows to be updated using a condition in the
WHERE
clause. TheWHERE
clause is optional. If you omit it, theUPDATE
statement will modify all rows in the table.
Notice that the WHERE
clause is so important that you should not forget. Sometimes, you may want to update just one row; However, you may forget the WHERE
clause and accidentally update all rows of the table.
Note: Be careful when updating records in a table! Notice the WHERE
clause in the UPDATE
statement. The WHERE
clause specifies which record(s) that should be updated. If you omit the WHERE
clause, all records in the table will be updated!
MySQL supports two modifiers in the UPDATE
statement.
- The
LOW_PRIORITY
modifier instructs theUPDATE
statement to delay the update until there is no connection reading data from the table. TheLOW_PRIORITY
takes effect for the storage engines that use table-level locking only such asMyISAM
,MERGE
, andMEMORY
. - The
IGNORE
modifier enables theUPDATE
statement to continue updating rows even if errors occurred. The rows that cause errors such as duplicate-key conflicts are not updated.
What is UPDATE command?
Update command is a data manipulation command which is used to edit the records of a table. It may be used to update a single row based on a condition, all rows or set of rows based on the condition given by the user.
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
UPDATE Table
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.
Example
UPDATE ;Customers<br>SET ;ContactName =;'Alfred Schmidt', City =;'Frankfurt'<br>WHERE ;CustomerID =;1;
The selection from the “Customers” table will now look like this:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
UPDATE Multiple Records
It is the WHERE
clause that determines how many records will be updated. The following SQL statement will update the PostalCode to 00000 for all records where country is “Mexico”:
Example
UPDATE ;Customers<br>SET ;PostalCode = 00000<br>WHERE Country =;'Mexico';
The selection from the “Customers” table will now look like this:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 00000 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 00000 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
Update Warning!
Be careful when updating records. If you omit the WHERE
clause, ALL records will be updated!
Example
UPDATE ;Customers<br>SET;PostalCode =00000;
The selection from the “Customers” table will now look like this:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 00000 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 00000 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 00000 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | 00000 | UK |
MySQL UPDATE
examples
Let’s practice the UPDATE
statement.
1) Using MySQL UPDATE
to modify values in a single column example
See the following employees
table from the sample database.

In this example, we will update the email of Mary Patterson
to the new email mary.patterso@classicmodelcars.com
.
First, find Mary’s email from the employees
table using the following SELECT
statement:
<code>SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Second, update the email address of Mary
to the new email mary.patterson@classicmodelcars.com
:
<code>UPDATE employees SET email = 'mary.patterson@classicmodelcars.com' WHERE employeeNumber = 1056;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
MySQL issued the number of rows affected:
1 row(s) affected
In this UPDATE
statement:
- The
WHERE
clause specifies the row with employee number1056
will be updated. - The
SET
clause sets the value of theemail
column to the new email.
Third, execute the SELECT
statement again to verify the change:
<code>SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

2) Using MySQL UPDATE
to modify values in multiple columns
To update values in the multiple columns, you need to specify the assignments in the SET
clause. For example, the following statement updates both last name and email columns of employee number 1056:
<code>UPDATE employees SET lastname = 'Hill', email = 'mary.hill@classicmodelcars.com' WHERE employeeNumber = 1056;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
Let’s verify the changes:
<code>SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

3) Using MySQL UPDATE
to replace string example
The following example updates the domain parts of emails of all Sales Reps
with office code 6
:
<code>UPDATE employees SET email = REPLACE(email,'@classicmodelcars.com','@mysqltutorial.org') WHERE jobTitle = 'Sales Rep' AND officeCode = 6;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
4) Using MySQL UPDATE
to update rows returned by a SELECT
statement example
You can supply the values for the SET
clause from a SELECT
statement that queries data from other tables.
For example, in the customers
table, some customers do not have any sale representative. The value of the column saleRepEmployeeNumber
is NULL
as follows:
<code>SELECT customername, salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

We can take a sale representative and update for those customers.
To do this, we can select a random employee whose job title is Sales Rep
from the employees
table and update it for the employees
table. This query selects a random employee from the table employees
whose job title is the Sales Rep
.
<code>SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' ORDER BY RAND() LIMIT 1;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
To update the sales representative employee number column in the customers
table, we place the query above in the SET
clause of the UPDATE
statement as follows:
<code>UPDATE customers SET salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' ORDER BY RAND() LIMIT 1) WHERE salesRepEmployeeNumber IS NULL;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
If you query data from the employees
table, you will see that every customer has a sales representative. In other words, the following query returns no row.
SELECT salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NULL;