Share on


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>
<column1> = <value1>,
<column2> = <value2>

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 (
latest_film VARCHAR(200)
title VARCHAR(200) NOT NULL,
director_id INT REFERENCES director(id),
release_date DATE NOT NULL
INSERT INTO director (name)
INSERT INTO film (title, director_id, release_date)
('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 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 (
film f1
film f2
f2.director_id = f1.director_id
ORDER BY f2.release_date DESC LIMIT 1))
director, latest_films
director.latest_film = latest_films.title
WHERE = 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)


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.

About the Author(s)
Justin Ellingwood

Justin Ellingwood

Justin has been writing about databases, Linux, infrastructure, and developer tools since 2013. He currently lives in Berlin with his wife and two rabbits. He doesn't usually have to write in the third person, which is a relief for all parties involved.