MySQL UPDATE

This article describes how to use MySQL UPDATE statement to update one or more rows.

If you want to update existing rows in a table, you can use the UPDATE statement.

UPDATE syntax

The UPDATE statement can update one or more columns in one or more rows. Here is the basic syntax of the UPDATE statement:

UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
[WHERE clause];

Here:

  • You should specify the name of the table whose rows will be updated after the UPDATE keyword.
  • Use SET clause to set new values of one or more columns. Use commas to separate multiple columns. The column value can be an literal value, an expression, or a subquery.
  • Use WHERE clause to filter the rows that will be updated.
  • The WHERE clause is optional. If you do not specify a WHERE clause, all rows in the table is updated.

In a UPDATE Statement, the WHERE clause is very important. Please do not omit WHERE clauses.

UPDATE example

Let us show you a couple of examples of UPDATE.

In the following examples, we use the customer table from Sakila sample database as demonstrations.

Update one column value

In this example, we will change the customer’s email to [email protected] whose customer_id value is 1.

  1. Use the following SELECT statement to show the current values.

    SELECT first_name, last_name, email
FROM customer
WHERE customer_id = 1;

    +------------+-----------+-------------------------------+
| first_name | last_name | email                         |
+------------+-----------+-------------------------------+
| MARY       | SMITH     | [email protected] |
+------------+-----------+-------------------------------+
1 row in set (0.00 sec)

  2. Use the following UPDATE statement to update the email value.

    UPDATE customer
SET email = '[email protected]'
WHERE customer_id = 1;

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

    In this UPDATE statement:

    • WHERE clause specifies the filtering condition: customer_id = 1.
    • to Set email new value in the SET clause.

    In the UPDATE return output:

    • 1 row affected indicates that 1 row is affected. That is, 1 row is updated.
    • Rows matched: 1 indicates the number of rows which matchs WHERE condition.
    • Changed: 1 indicates that the number of updated rows.
    • Warnings: 0 indicates that there is not Warnings.

  3. Use the following SELECT statement to show row after updated.

    SELECT first_name, last_name, email
FROM customer
WHERE customer_id = 1;

    +------------+-----------+-----------------------------------+
| first_name | last_name | email                             |
+------------+-----------+-----------------------------------+
| MARY       | SMITH     | [email protected] |
+------------+-----------+-----------------------------------+
1 row in set (0.01 sec)

Update multiple columns values

In this example, we will update the customer first_name, last_name, email values at the same time.

UPDATE customer
SET first_name = 'Tim',
    last_name = 'Duncan',
    email = '[email protected]'
WHERE customer_id = 1;

Then, we check the result:

SELECT first_name, last_name, email
FROM customer
WHERE customer_id = 1;

+------------+-----------+-------------------------------+
| first_name | last_name | email                         |
+------------+-----------+-------------------------------+
| Tim        | Duncan    | [email protected] |
+------------+-----------+-------------------------------+
1 row in set (0.00 sec)

Update using expression

In the UPDATE statment, the column value can be set to an expression, function and so on.

The following statment updates e-mail domain part of all customers:

UPDATE customer
SET email = REPLACE(email, 'sakilacustomer.org', 'sqliz.com');

Query OK, 599 rows affected (0.03 sec)
Rows matched: 599  Changed: 599  Warnings: 0

Note that there is not a WHERE clause in the statement, so all rows in the table have been updated.

Update using subquery

The following example shows how to set a random store for customers who have not been binded to a store.

UPDATE customer
SET store_id = (
    SELECT store_id
    FROM store
    ORDER BY RAND()
    LIMIT 1
  )
WHERE store_id IS NULL;

In this example, the following SELECT statement returns a random store id:

SELECT store_id
FROM store
ORDER BY RAND()
LIMIT 1

In the SET clause, the store_id value is set to the store id retured of the above sub-queries.

UPDATE modifier

In MySQL, UPDATE statements support 2 modifiers:

  • LOW_PRIORITY: If you specify LOW_PRIORITY modifier, MySQL server will delay the execution of the DELETE operation until there are no clients there are no clients who read on the table.

    LOW_PRIORITY modifier is supported by those storage engines which only has table-level locking, such as: MyISAM, MEMORY, and MERGE.

  • IGNORE: If you specify a IGNORE modifier, MySQL server will perform UPDATE ignore those errors can be ignored during the operation. These errors return as WARNING.

Here is the usage of modifiers:

UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name = value

Conclusion

In this article, you learned how to update rows using MySQL UPDATE statement. The following are the main points of the UPDATE statement:

  • You should specify the name of the table whose rows will be updated after the UPDATE keyword.
  • Use SET clause to set new values of one or more columns. Use commas to separate multiple columns. The column value can be an literal value, an expression, or a subquery.
  • Use WHERE clause to filter the rows that will be updated.
  • The WHERE clause is optional. If you do not specify a WHERE clause, all rows in the table is updated.
  • The UPDATE statements supports LOW_PRIORITY and IGNORE modifiers. If you want to ignore some wrong line, you can use the IGNORE modifier.