Updating Data by Using SQL UPDATE statement

SQL UPDATE statement is used to update existing data in a data table. It can be used to change values of single row, group of rows or even all rows in a table. In MySQL, the SQL UPDATE statement form is as follows:

UPDATE [LOW_ PRIORITY] [IGNORE] table_name [, table_name...]
SET column_name1=expr1 [, column_name2=expr2 ...]
[WHERE condition]

The UPDATE keyword is followed by the name of a table you want to change data. In MySQL, you can change the data of many tables at a time. If an UPDATE statement violates an integrity constraint, MySQL does not perform the update and it will return an error message.

The SET clause determines the column(s) and the changed values. The changed values could be a constant value expression or even a subquery.

WHERE clause determines which rows of the tables will be updated. It is an optional part of SQL UPDATE statement. If WHERE clause is ignored, all rows in the tables will be updated. In some cases, you should take care about unexpected situation when working with SQL UPDATE statement. Sometime, you want to change just one row of a table but you forget WHERE clause so you accidentally update the whole table.

LOW_ PRIORITY keyword is used to delay the execution until no other client applications reading data from the table.

IGNORE keyword is used to execute the update even error(s) can be occurred during execution. Errors could be duplicated value on unique column, or new data does not match with the column data type. In the first situation data are not updated and in the second one MySQL try to convert the data into closest valid values.

Let's practice with SQL UPDATE statement in our sample database. In employees table, if you want to update the email of Mary Patterson with employeeNumber 1 with the new email as mary-patterso@classicmodelcars.com, you can execute this query:

Before updating

SELECT firstname,lastname,email
FROM employees
WHERE employeeNumber = 1

+-----------+-----------+--------------------------------+
| lastname  | firstname | email                          |
+-----------+-----------+--------------------------------+
| Patterson | Mary      | mpatterso@classicmodelcars.com |
+-----------+-----------+--------------------------------+
1 row in set (0.02 sec)

Update her email to the new email as mary-patterso@classicmodelcars.com

UPDATE employees
SET email = 'mary-patterso@classicmodelcars.com'
WHERE employeeNumber = 1

Execute the select query above again; you will see the email change to

+-----------+-----------+------------------------------------+
| lastname  | firstname | email                              |
+-----------+-----------+------------------------------------+
| Patterson | Mary      | mary-patterso@classicmodelcars.com |
+-----------+-----------+------------------------------------+
1 row in set (0.00 sec)