1.4.12.4 The UPDATE Statement

The UPDATE statement is used to modify the data in a table.

Syntax:

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

1.4.12.4.1 Update one Column in a Row

Person table

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
| 'Blair'  | 'Tony'    | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

We want to add a change Tony Blair's first name to James:

UPDATE Person SET FirstName = 'James' WHERE LastName = 'Blair'

Person table

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

1.4.12.4.2 Update several Columns in a Row

We want to change the number of everyone with a FirstName John and make their DateOfBirth all 1980-01-01:

UPDATE Person SET Number = 1, DateOfBirth = '1980-01-01' WHERE FirstName = 'John'

Result:

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+