Before we begin, we assume that you have installed a MySQL server on your system and can access a specific database. If you need a sample database to work with, consider the resource provided below:
https://dev.mysql.com/doc/index-other.html
In the resource above, you will get a zipped archive of the sample database you can use to test the concepts in this tutorial.
Basic Usage
The basic usage of the MySQL UPDATE statement is simple and you can use it to update the columns of the existing rows in a specified table with the set values.
The basic syntax of the UPDATE statement is:
SET assignment_list
[WHERE condition];
We start by calling the UPDATE statement followed by modifiers (continue reading to learn more) and the table name.
In the second statement, we set the column we wish to update and the values we need to change. You can update multiple columns by specifying the assignments in the form of an expression or comma-separated values.
To avoid updating all the rows in the entire table, we specify a condition restricting the UPDATE command. For example, this could be WHERE id=2, etc. We use the WHERE statement followed by the condition as shown in the third line.
You can also set an ORDER BY clause that forces MySQL to update the rows in the order specified.
Modifiers
MySQL UPDATE statement supports two modifiers—as seen in the above example. These are:
- LOW_PRIORITY: This modifier tells the UPDATE query to delay the operation until no connections are reading from the specified table.
- IGNORE: The IGNORE modifier allows the UPDATE query to continue updating even if an error occurs.
MySQL Update Use Cases
Let us now consider an illustration of updating values using the UPDATE command. We will start with a simple one where we update a single column.
Update Single Column
If you are using the Sakila sample database, consider the film table with sample information as shown below:
+----------------------+
| Field |
+----------------------+
| film_id |
| title |
| description |
| release_year |
| language_id |
| original_language_id |
| rental_duration |
| rental_rate |
| length |
| replacement_cost |
| rating |
| special_features |
| last_update |
+----------------------+
If we query the data stored in that table as shown in the query below (the Sakila database contains lots of information; ensure to limit when querying some tables.)
You will get sample information as shown in the table below:
NOTE: It is good to have a visual reference of how your database is organized to ensure no errors occur or to perform invalid queries.
Now that we know what the database looks like, we can start to update a specific column. In this example, let us update the rating of the film ACADEMY DINOSAUR to a value “PG-13.”
Consider the query shown below:
SET rating=”PG-13”
WHERE
film_id=1;
Once the above query executes, the value of the ratings for the movie where id=1 is set to PG-13.
You can confirm using the query shown below:
+--------+
| rating |
+--------+
| PG-13 |
| G |
| NC-17 |
+--------+
3 rows in set (0.00 sec)
Update Multiple Columns
Updating multiple columns is similar to updating a single command, but you specify multiple values in the SET clause as shown in the query below:
In this example, we are updating the second movie values to the rating of PG-13 and the rate of 1.99.
Confirm the changes have applied successfully:
+-------------+--------+
| rental_rate | rating |
+-------------+--------+
| 0.99 | PG-13 |
| 1.99 | PG-13 | <------------
+-------------+--------+
2 rows in set (0.00 sec)
As you can see, you can add multiple columns by separating their values in commas.
Conclusion
In this tutorial, you learned how to use the MySQL UPDATE command to change values in a table column.
If you need more experience with MySQL, consider our tutorials provided below:
