Summary: Updating existing data is one of the most important task when you work with database. In this tutorial, you will learn how to use MySQL UPDATE statement to update data in database tables.
SQL UPDATE statement is used to update existing data in database tables. 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 below:
1 |
UPDATE [LOW_ PRIORITY] [IGNORE] table_name [, table_name...] |
3 |
[, column_name2=expr2 ...] |
Let’s examine the MySQL UPDATE statement in details:
- Followed by the UPDATE keyword is the name of a table you want to change the data. In MySQL, you can change the data of multiple tables at a time. If an UPDATE command violates any integrity constraint, MySQL does not perform the update and it will issue an error message.
- The SET clause determines the columns’ name 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. The WHERE clause is so important that you should not forget. Sometimes 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. This is used for controlling the update process in MySQL database server.
- IGNORE keyword is used to execute the update even errors occurred during the execution. The errors in the update process could be duplicate value on unique column, or new data does not match with the column data type. In the first situation data is not updated and in the second one MySQL tries to convert the data into closest valid values.
Let’s practice with a couple of examples in our sample database to understand more about SQL UPDATE statement.
In employees table, if you want to update the email of Mary Patterson with employeeNumber 1 with the new email as mary-patterso@sdfsdf.com, you can execute the following query:
Make sure we are truly updating the data we select it first to see what the current data looks like.
5 |
WHERE employeeNumber = 1 |
+-----------+-----------+--------------------------------+
| lastname | firstname | email |
+-----------+-----------+--------------------------------+
| Patterson | Mary | mpatterso@sdfsdf.com |
+-----------+-----------+--------------------------------+
1 row in set (0.02 sec)
Update her email to the new email mary-patterso@sdfsdf.com
2 |
SET email = 'mary-patterso@classicmodelcars.com' |
3 |
WHERE employeeNumber = 1 |
Execute the select query above again; you will see the email change to the new value.
+-----------+-----------+------------------------------------+
| lastname | firstname | email |
+-----------+-----------+------------------------------------+
| Patterson | Mary | mary-patterso@sdfsdf.com |
+-----------+-----------+------------------------------------+
1 row in set (0.00 sec)
In this tutorial, you’ve learned how to use SQL UPDATE statement in MySQL to update data of database tables.
Related posts:
- Inserting Data into Database Tables
- Using MySQL SELECT Statement to Query Data
- Working with Tables – Part II
- How to Use MySQL Limit to Constrain Number of Returned Records
- Retrieving Data in a Range Using SQL BETWEEN
