Expert Update Statements with Joins on MariaDB

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.

Update statement with multiple joins shown in a database manager

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

See also

Leave a Reply

Your email address will not be published.