# How to update existing data in MySQL

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

The `UPDATE` command allows you to change the data in existing MySQL records. This guide demonstrates how to use the `UPDATE` operation to modify the values within your tables.

Canonical URL: https://www.prisma.io/dataguide/mysql/inserting-and-modifying-data/updating-existing-data
Authors: Justin Ellingwood
Content revision: d63a2962984e4d7c087fa26169ff1009afa41a14fcb2bf7e1baeeeb77cc2fed6
Source revision: 1fe7ef3d9287b7c3aadd4cd0d2fe478dafd71727

## 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](https://www.prisma.io/dataguide/intro/database-glossary#record) 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:

```sql
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](https://www.prisma.io/dataguide/intro/database-glossary#join). The basic syntax looks like this:

```sql
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`.

**Expand to see the commands to create and populate these tables**

```sql
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:

```sql
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:

```sql
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.