You can use UPDATE statement in Structured Query Language (SQL) to modify values in the matching rows. However, only some people know that you can use JOINs in UPDATE queries. For example, by using JOINs we can retrieve additional data from other tables or exclude some rows from our update. Soon you will learn how to write update statements with joins on MariaDB / MySQL.
Simple UPDATE query
A simple UPDATE query has 3 parts: table reference, set part and where part. For example, if we are changing the value in the name column of the row with id = 3 in the user table, we write the following:
UPDATE
user
SET
name = 'John'
WHERE
id = 3
Update Statements with Joins on MariaDB
Adding a join to our simple update query is straightforward. All we have to do is add our join to the table reference part of our update statement. So let’s use the same example and also set country_name field to name field in country table.
UPDATE
user AS u
INNER JOIN
country AS c ON c.code = u.country_code
SET
u.name = 'John',
u.country_name = c.name
WHERE
u.id = 3
Because we used an INNER JOIN, if the country table does not have any matching rows, the changes won’t be applied. So if you want to apply your changes anyway, with the risk of country_name being set to null, you should use a LEFT JOIN instead.
Multi-table Update Statement using Joins
You can go one step further and update multiple tables in a single update statement using joins. Using the same example, let’s say we also want to set country_name field on the login table.
UPDATE
user AS u
INNER JOIN
country AS c ON c.code = u.country_code
INNER JOIN
login AS l ON l.email = l.email
SET
u.name = 'John',
u.country_name = c.name,
l.country_name = c.name
WHERE
u.id = 3
Like the previous example, if login table doesn’t have the email of the user with id = 3, MariaDB doesn’t update any of the rows. As you can expect this query updates 2 rows, one in user table, another in login table.
Test data
Would you like to test these queries in your own database? Import test fixtures using the SQL file below and try the update statements in the examples.
Final thoughts
Using joins in update statements is very useful. Main thing to keep in mind is that the joins come right after the main table just like it is in select statements. Hope this guide has been useful to you. Comment below if you have any trouble with this guide.
References
- UPDATE – MariaDB Knowledge Base
- SELECT – MariaDB Knowledge Base
- JOIN Syntax – MariaDB Knowledge Base
- MySQL UPDATE JOIN | Cross-Table Update in MySQL – mysqltutorial.org