MySQL UPDATE Statement

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. The WHERE clause is optional. If you omit it, the UPDATE 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 the UPDATE statement to delay the update until there is no connection reading data from the table. The LOW_PRIORITY takes effect for the storage engines that use table-level locking only such as MyISAM, MERGE, and MEMORY.
The IGNORE modifier enables the UPDATE 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 =&nbsp;00000<br>WHERE&nbsp;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	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	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 number 1056 will be updated.
The SET clause sets the value of the email 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;

MySQL UPDATE Statement

What is the MySQL UPDATE Statement?

UPDATE Syntax

What is UPDATE command?

Demo Database

UPDATE Table

Example

UPDATE Multiple Records

Example

Update Warning!

Example

MySQL `UPDATE` examples

1) Using MySQL `UPDATE` to modify values in a single column example

2) Using MySQL `UPDATE` to modify values in multiple columns

3) Using MySQL `UPDATE` to replace string example

4) Using MySQL `UPDATE` to update rows returned by a `SELECT` statement example

Category Posts

Categories

Recent Posts

MySQL UPDATE Statement

What is the MySQL UPDATE Statement?

UPDATE Syntax

What is UPDATE command?

Demo Database

UPDATE Table

Example

UPDATE Multiple Records

Example

Update Warning!

Example

MySQL UPDATE examples

1) Using MySQL UPDATE to modify values in a single column example

2) Using MySQL UPDATE to modify values in multiple columns

3) Using MySQL UPDATE to replace string example

4) Using MySQL UPDATE to update rows returned by a SELECT statement example

Category Posts

Categories

Recent Posts

MySQL `UPDATE` examples

1) Using MySQL `UPDATE` to modify values in a single column example

2) Using MySQL `UPDATE` to modify values in multiple columns

3) Using MySQL `UPDATE` to replace string example

4) Using MySQL `UPDATE` to update rows returned by a `SELECT` statement example