DML - MySQL (UPDATE)

Update Data on Table

Synatx

UPDATE <table_name> SET field1 = new_value, fieldN = new_value_N WHERE <condition>;

Example

• The records available in table student.

+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
|          3 | Ben          |   15 | M      |
|          4 | Jane         |   15 | F      |
+------------+--------------+------+--------+

• Update Jane to 16 years old

Usage

mysql> UPDATE student SET age = 16 
    -> WHERE student_name = "Jane" 
    -> AND student_id = 4;

Output

Results:

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

Validation:

mysql> SELECT * FROM student;
+------------+--------------+------+--------+
| student_id | student_name | age  | gender |
+------------+--------------+------+--------+
|          1 | Kai          |   15 | M      |
|          2 | John         |   15 | M      |
|          3 | Ben          |   15 | M      |
|          4 | Jane         |   16 | F      |
+------------+--------------+------+--------+
4 rows in set (0.00 sec)

Conclusion

• If we update without condition, it will update all of the data/records in a table.
• If multiple condition can use AND, OR to join.