Login | Contact Us | Site Map | Resources

Data Manipulation

Home > MYSQL Tutorial > Updating MYSQL Data

Updating MYSQL Data

Often, data that is stored in your MYSQL table will need to be changed or updated/replaced with new data. The UPDATE statement is used for this purpose.

The syntax is: UPDATE tablename SET column = newdata WHERE column LIKE currentdata;

You need to specify which table needs updates, and which column(s) in the table need updated. Unless you want that column updated (with the same data) in every single record in the table, you will also need to identify the row/record that you want to update.

Let's consider the following example:

mysql> SELECT * FROM pets;
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
| id | name      | breed     | gender | dateofbirth | description        | pedigree | medicalrecord |
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
| 1  | Chester   | Shiba Inu | Male   | 09-30-2008  | Fluffy             | Unknown  |               |
| 2  | Tamika    | Ferret    | Female | 03-12-2010  | Slim               | Unknown  |               |
| 3  | Sylvester | Goldfish  | Male   | 01-15-2011  | Slippery, Orangish | Unknown  |               |
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
3 rows in set (0.00 sec)

mysql> UPDATE pets SET description = 'Muddy' WHERE name LIKE "Chester";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE pets SET description = 'Chubby', pedigree = 'None' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM pets;
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
| id | name      | breed     | gender | dateofbirth | description        | pedigree | medicalrecord |
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
| 1  | Chester   | Shiba Inu | Male   | 09-30-2008  | Muddy              | Unknown  |               |
| 2  | Tamika    | Ferret    | Female | 03-12-2010  | Chubby             | None     |               |
| 3  | Sylvester | Goldfish  | Male   | 01-15-2011  | Slippery, Orangish | Unknown  |               |
+----+-----------+-----------+--------+-------------+--------------------+----------+---------------+
3 rows in set (0.00 sec)

mysql>

As you can see, after selecting all of the data in our pets table so that we would view it, two UPDATE statements were successfully performed.

The first UPDATE statement set the description to "muddy" in the pets table where the pet's name was "Chester".

The second UPDATE statement set the description to "Chubby" AND set the pedigree to "None" (notice how the two are comma separated) in the pets table where the record id was equal to 2.

So as you can see, updating a record is as simple as knowing what data you want updated, and pinpointing the exact record where it needs updated.

It is possible to update multiple records at one time. If, for example, you have multiple Chesters, it might not have rained on all of them, so always think ahead. The id column was added for the purpose of uniquely identifying each row, so it is usually the safest and most practical option.