Update Data in a MySQL Database
This page explains how to update existing data in a MySQL database.
So we've already added data to our database. But now we realize that our data contains a mistake. Apples have been assigned a UnitId of 1 — but this should be 2.
You can see this here:
No problem. We'll just update that record.
The UPDATE Statement
The SQL UPDATE statement allows us to update the data in our database. We can use this statement to change the unit ID from 1 to 2. To do this, we use the WHERE clause to specify the exact record we need to update. Like this:
Generally, it's advisable when doing an UPDATE operation, to specify the ID field of the record you're attempting to update (or whatever its primary key is). This helps guard against accidentally updating the wrong record/s. In this example we knew that apples had an FruitId of 1. But you might not always know the ID of the record you're trying to update. In that case you could do something like this:
We can follow either of those up with a SELECT statement and here's the result:
Note that the DateUpdated column has been updated too, even though we didn't specify an update for that column. This is because, when we created the table, we set that column to be updated with the current date/time every time there was an update to the record.
Here's the code we used when we created that column:
More specifically, it is the ON UPDATE CURRENT_TIMESTAMP that resulted in the column being updated just now when we ran the UPDATE statement.
Safe Updates
We could just as easily have constructed our UPDATE command like this (without AND UnitId = 1):
However, you might encounter the following error if you try to do that:
Safe Updates Mode
If you encounter the above error, it's because your MySQL connection is running in Safe Updates mode. This helps prevent us from overwriting large amounts of data accidentally. In fact, if we had forgotten to include the WHERE clause we would have updated every single record in the table!
Yes, running the following code will result in every record in our table being updated to Banana:
So there is a real benefit to be had by running in Safe Updates mode.
However, if you really do need to execute that query (or if all your fruit really has turned into bananas), you can do one of the following:
- Modify your query to include a
KEYcolumn. We did this in the examples at the top of this page. - Disable Safe Updates mode.
Disable Safe Updates Mode
If you find that you need to perform an UPDATE operation without using a KEY field, you can always disable Safe Updates mode.
To disable Safe Updates mode, run the following command prior to running your UPDATE statement:
It's always a good idea to restore the setting to its previous state once you're done — especially with settings that could have a widespread impact like this one.
To enable Safe Updates mode, run the following:
In fact, you could run everything at the same time, like this:
Update Multiple Fields
You can update multiple fields by using a comma to separate each field assignment. Like this:
Result:
