MySQL | UPDATE
Start your free 7-days trial now!
The UPDATE statement is used to modify existing records in a table.
You need the UPDATE privilege on columns whose values you wish to update.
Syntax
UPDATE table_nameSET column1 = value1, column2 = value2 ...WHERE condition;
Examples
Consider the following table about whether some students hold a drivers license or not:
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.
Single column update
To update Sky Towner's age to 18 as today is his birthday:
UPDATE drivers_licenseSET age = 18WHERE student_id = 1;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
The WHERE clause here specifies that we only want to update the age for Sky Towner.
We can see that Sky Towner's age is now updated to 18:
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 |+------------+---------------+------+-------------+
Take care whenever you are performing an update on your table. If we forget to add the WHERE clause in the above example, the age for all students in the table will be set to 18.
To set the age of all students to 18:
UPDATE drivers_licenseSET age = 18;SELECT *FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 1 | Sky Towner | 18 | 0 || 2 | Ben Davis | 18 | 1 || 3 | Travis Apple | 18 | 0 || 4 | Arthur David | 18 | 0 || 5 | Benjamin Town | 18 | 1 |+------------+---------------+------+-------------+
Multiple column update
To update the age and has_license column values for student_id=1:
UPDATE drivers_licenseSET age = 18, has_license = TRUEWHERE student_id = 1;SELECT *FROM drivers_license;
+------------+---------------+------+-------------+| student_id | full_name | age | has_license |+------------+---------------+------+-------------+| 1 | Sky Towner | 18 | 1 || 2 | Ben Davis | 19 | 1 || 3 | Travis Apple | 18 | 0 || 4 | Arthur David | 16 | 0 || 5 | Benjamin Town | 17 | 1 |+------------+---------------+------+-------------+
As demonstrated above, multiple column updates are performed by specifying comma separated column=value pairs.
Update with respect to current value
To update the age of student 1 by incrementing the current value by 1:
UPDATE drivers_licenseSET age = age + 1WHERE student_id = 1;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 |+------------+---------------+------+-------------+
Note that Sky Towner's age has been incremented by 1 from 17 to 18.
