MySQL UPDATE Statement

Last Updated: 27 July, 2023

The MySQL UPDATE statement is used to modify existing records in a table. It allows us to update one or more columns of a single row or multiple rows in a table.

Here's the basic syntax of the UPDATE statement:

  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

Let's break down the syntax:

1. UPDATE: Specifies that we want to update records in a table.
2. table_name: The name of the table we want to update.
3. SET: Specifies the columns we want to update and their new values.
4. column1 = value1, column2 = value2, ...: Specifies the column-value pairs we want to update. We can update one or more columns at a time.
5. WHERE: Specifies the conditions that must be met for the update to occur. It determines which records will be updated.
6. condition: The condition that specifies which records should be updated. If we omit the WHERE clause, all records in the table will be updated.

Here's an example that demonstrates how to use the UPDATE statement in MySQL.

Suppose we have a table called employees with the following structure:

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  +----+-----------+------------+--------+------------+---------------+

Example 1: Update single column of one row

In this example, we will see how to update a single column of one row in a table.

  mysql> UPDATE employees
         SET Salary = 50000
         WHERE ID = 2;
  Query OK, 1 row affected (0.54 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

  mysql> select * from employees;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  50000 | 2021-01-23  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  +----+--------+------------+------+--------+-------------+
  5 rows in set (0.00 sec)

In this example, we have updated the salary column of the employees table for the employee with ID equal to 2. Before, the salary was 38000 but after the update, it is now 50000.

Example 2: Update single column of multiple rows

In this example, we will see how to update a multiple columns of one row in a table.

  mysql> UPDATE employees
         SET Department = 'Finance', Age = 55
         WHERE ID = 3;
  Query OK, 1 row affected (0.14 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

  mysql> select * from employees;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  50000 | 2021-01-23  |
  |  3 | Vimal  | Finance    |   55 |  68000 | 2022-04-08  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  +----+--------+------------+------+--------+-------------+
  5 rows in set (0.00 sec)

In this example, we have updated the Department and Age columns of the employees table for the employee with ID equal to 3. Before, the Department was Sales and Age was 52 but after the update, Department is Finance and Age is 55.

Example 3: Update multiple columns of multiple rows

In this example, we will see how to update a multiple columns of multiple rows in a table.

  mysql> UPDATE employees
         SET Department = 'Operation', Salary = 20000
         WHERE Age > 35 AND Salary > 50000;
  Query OK, 3 rows affected (0.21 sec)
  Rows matched: 3  Changed: 3  Warnings: 0

  mysql> select * from employees;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Operation  |   43 |  20000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  3 | Vimal  | Operation  |   52 |  20000 | 2022-04-08  |
  |  4 | Hina   | Operation  |   38 |  20000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  +----+--------+------------+------+--------+-------------+
  5 rows in set (0.00 sec)

In this example, we have updated the Department and Salary columns of the employees table for the employee with an age greater than 35 and a salary greater than 50000.

Example 4: Update without WHERE clause

If we need to update all the records of a table without any conditions, we can omit the WHERE clause from the UPDATE statement in MySQL.

  mysql> UPDATE employees
         SET Department = 'Finance', Salary = 40000;
  Query OK, 5 rows affected (0.21 sec)
  Rows matched: 5  Changed: 5  Warnings: 0

  mysql> select * from employees;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Finance    |   43 |  40000 | 2023-06-11  |
  |  2 | Kamal  | Finance    |   35 |  40000 | 2021-01-23  |
  |  3 | Vimal  | Finance    |   52 |  40000 | 2022-04-08  |
  |  4 | Hina   | Finance    |   38 |  40000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  40000 | 2010-02-24  |
  +----+--------+------------+------+--------+-------------+
  5 rows in set (0.00 sec)

In this example, we have not used WHERE clause so all the rows of a table have been updated.

Note that we need appropriate privileges to execute an UPDATE statement on a table.