Menu
Share on

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
RELATED ON PRISMA.IO

To update data with Prisma Client, issue an update query.

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.