Update the contents of a field

One of the most common tasks when working with MySQL databases is the data update. To update records in an MySQL table, the UPDATE statement is used. Here is the syntax:

UPDATE table_name SET column1=value1,column2=value2,... WHERE column_name operator value

As you can see from the syntax above, you first need to specify the table name. Second, you need to specify which columns will be modified and their new values after the SET clause. Lastly, you need to specify which rows will be updated using the WHERE clause.

Here is an example. We have our old table testtb:

mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 +------+-------------+------+
 3 rows in set (0.00 sec)

Let’s say that Amy Goodridge married and changed her surname to Bryant. To update the surname field, we can use the following syntax:

mysql> UPDATE testtb SET surname='Bryant' WHERE surname='Goodridge';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Bryant | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 +------+-------------+------+
 3 rows in set (0.00 sec)