MySQL
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 table_name
UPDATE table_name SET column_name = value WHERE condition;
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)
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)
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)
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.
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)
mysql> UPDATE employee SET score = 7, lastmodified = '2018-02-04 06:08:10';
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)