Updating rows in a table in MySQL
Start your free 7-days trial now!
We can update the information in a table in MySQL by using the UPDATE statement. To update multiple rows at once, we can make use of a INSERT INTO statement with a ON DUPLICATE KEY UPDATE construction.
Updating a single row
Given the below sample table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
To update the age of student Sky Towner to 18:
UPDATE drivers_licenseSET age = 18WHERE student_id = 1;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
To check that age has been updated to 18 for Sky Towner:
SELECT *FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 1 | Sky Towner | 18 | 0 || 2 | Ben Davis | 19 | 1 || 3 | Travis Apple | 18 | 0 || 4 | Arthur David | 16 | 0 || 5 | Benjamin Town | 17 | 1 |+------------+---------------+------+-------------+
Updating multiple rows
Given the below sample table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
To update the full_name of both student_id=1 and student_id=2:
INSERT INTO drivers_license (student_id, full_name)VALUES (1, "Emma Parker"), (2, "Fiona Park")ON DUPLICATE KEY UPDATE full_name = VALUES (full_name);
Query OK, 4 rows affected (0.00 sec)Records: 2 Duplicates: 2 Warnings: 0
Essentially we are inserting new records into the table, however, if the student_id we are trying to insert already exists in the table then instead of inserting a new record we simply update the full_name of the existing record. In this case as student_id=1 and student_id=2 already existed in the table, the full_name of these records was updated.
SELECT * FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 1 | Emma Parker | 17 | 0 || 2 | Fiona Park | 19 | 1 || 3 | Travis Apple | 18 | 0 || 4 | Arthur David | 16 | 0 || 5 | Benjamin Town | 17 | 1 |+------------+---------------+------+-------------+
Although we only updated two records here, we can use similar syntax to update as many records as we like.
