MySQL / Inserting and modifying data
How to update existing data in MySQL
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.
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
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
table2, but only in rows where
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
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
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
WITH latest_films AS (SELECTf1.*FROMfilm f1WHEREf1.id = (SELECTf2.idFROMfilm f2WHEREf2.director_id = f1.director_idORDER BY f2.release_date DESC LIMIT 1))UPDATEdirector, latest_filmsSETdirector.latest_film = latest_films.titleWHEREdirector.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)
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.