How to update existing data in MySQL

Introduction

Many database tables manage data that will need to be changed or updated from time to time. The SQL UPDATE command can help in these situations by allowing you to change the values stored in records within a table.

To update records, you must provide the columns where changes will occur and their new values. To tell MySQL which records to target, you need to also give match criteria so it can determine which row or rows to change. In this article, we'll discuss how to use UPDATE to change the values of your table data one at a time or in bulk.

Using UPDATE to modify data

The basic syntax of the UPDATE command looks something like this:

UPDATE <table>
SET
    <column1> = <value1>,
    <column2> = <value2>
WHERE
    <match_condition>;

As shown above, the basic structure involves three separate clauses:

• specifying a table to act on,
• providing the columns you wish to update as well as their new values, and
• defining criteria to determine which records to match

When successfully committed, MySQL confirms the action by outputting the number of rows matched and altered:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Updating records based on values in another table

Updates based on providing new external data are relatively straightforward. You just need to provide the table, the columns, the new values, and the targeting criteria.

However, you can also use UPDATE to conditionally update table values based on information stored in a joined table. The basic syntax looks like this:

UPDATE <table1>, <table2>
SET <table1>.<column1> = <table2>.<column1>
WHERE <table1>.<column2> = <table2>.<column2>;

Here, we are updating the value of column1 in the table1 table to the value stored in column1 of table2, but only in rows where column2 of table1 match column2 of table2. Even though the value is only changing in one table, we need to add both tables to the list of tables that UPDATE operates on. The WHERE construction specifies the join conditions to integrate the two tables.

As an example, suppose that we have two tables called film and director.

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    latest_film VARCHAR(200)
);

CREATE TABLE film (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    director_id INT REFERENCES director(id),
    release_date DATE NOT NULL
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

INSERT INTO film (title, director_id, release_date)
VALUES
    ('first movie', 1, '2010-08-24'),
    ('second movie', 1, '2010-12-15'),
    ('third movie', 2, '2011-01-01'),
    ('fourth movie', 2, '2012-08-02');

These two tables have a relation with film.director_id referencing director.id. Currently, the latest_film for the director table is NULL. However, we can populate it by with the director's latest film title using the WHERE clause to bring to bring the two tables together.

Here, we use a WITH clause to create a Common Table Expression (CTE) called latest_films that we can reference in our UPDATE statement:

WITH latest_films AS (
    SELECT
        f1.*
    FROM
        film f1
    WHERE
        f1.id = (SELECT
                     f2.id
                 FROM
                     film f2
                 WHERE
                     f2.director_id = f1.director_id
                 ORDER BY f2.release_date DESC LIMIT 1))
UPDATE
    director, latest_films
SET
    director.latest_film = latest_films.title
WHERE
    director.id = latest_films.director_id;

If you query the director table, it should show you each director's latest film now:

SELECT * FROM director;

+----+-------+--------------+
| id | name  | latest_film  |
+----+-------+--------------+
|  1 | frank | second movie |
|  2 | bob   | fourth movie |
|  3 | sue   | NULL         |
+----+-------+--------------+
3 rows in set (0.00 sec)

Conclusion

In this article, we've demonstrated how to use the UPDATE command to alter the values of existing MySQL records. The UPDATE command is very flexible when combined with other SQL constructs, allowing you to modify data in interesting ways according to conditions and values found throughout the database. As you get familiar with the operation, you will be able to find new ways of changing your data to match your requirements.