MySQL - UPDATE Table

In this tutorial we will learn to update data of a table in MySQL.

We use the UPDATE table_name command to update data in a table.

Update syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Update column of single row using primary key

In the following example we will be updating employee score of the employeeid 'e05'.

Before UPDATE:

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | bobcoder@example.com |     5 | 1900-08-20 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

UPDATE Query:

mysql> UPDATE employee
SET score = 8
WHERE employeeid = 'e05';

Output:

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

Employee data after UPDATE:

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | bobcoder@example.com |     8 | 1900-08-20 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

Update multiple columns of one row using primary key

To update multiple columns we separate them using comma.

In the following example we are updating the score and lastmodified column of employeeid 'e05'.

mysql> UPDATE employee
SET score = 7,
lastmodified = '2018-01-02 03:04:05'
WHERE employeeid = 'e05';

After update we get the following result.

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-01-02 03:04:05 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

Update column of multiple rows for a given match

In the following example we are updating the score and lastmodified data of all those employees having lastname 'Doe'.

mysql> UPDATE employee
SET score = 9,
lastmodified = '2018-01-02 03:04:05'
WHERE lastname = 'Doe';

We get the following output:

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The updated rows:

mysql> SELECT * FROM employee WHERE lastname = 'Doe';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e02        | John      | Doe      | johndoe@example.com |     9 | 1900-02-03 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com |     9 | 1900-05-20 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Update without any WHERE clause

If we don't provide the WHERE clause when updating then, it will updated all rows of the table.

Do not run UPDATE query without a WHERE condition. You will end up updating something that you never wanted.

Before UPDATE:

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     6 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     9 | 1900-02-03 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     7 | 1900-10-20 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-01-02 03:04:05 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

UPDATE Query:

mysql> UPDATE employee
SET score = 7,
lastmodified = '2018-02-04 06:08:10';

Output:

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

After UPDATE:

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     7 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     7 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     7 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)