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:

UPDATE Fruit

SET UnitId = 2

WHERE FruitId = 1;

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:

UPDATE Fruit

SET UnitId = 2

WHERE FruitName = 'Apple' AND UnitId = 1;

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:

DateUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

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):

UPDATE Fruit

SET UnitId = 2

WHERE FruitName = 'Apple';

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:

UPDATE Fruit

SET FruitName = '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 KEY column. 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:

set sql_safe_updates = 0;

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:

set sql_safe_updates = 1;

In fact, you could run everything at the same time, like this:

set sql_safe_updates = 0;

​

UPDATE Fruit

SET UnitId = 2

WHERE FruitName = 'Apple';

​

select * from Fruit;

​

set sql_safe_updates = 1;

Update Multiple Fields

You can update multiple fields by using a comma to separate each field assignment. Like this:

UPDATE Fruit

SET FruitName = 'Red Grapes', Inventory = '11'

WHERE FruitId = 5;

Result: