intro
Learn everything you need to know about the MySQL UPDATE JOIN statement for updating rows in a table based on data from another table.
Data in a relational MySQL database can be spread across several related tables. Using a JOIN query helps you retrieve that data in its entirety. But what if you want to update data in one table based on data from another related table? This is where a MySQL UPDATE JOIN query comes in!
In this guide, you will see how to use a MySQL UPDATE with JOIN, when it is useful, and more.
Let’s dive in!
MySQL UPDATE JOIN Query: Is It Possible?
TL;DR: Yes, it is possible to perform a query of UPDATE with JOIN in MySQL.
The UPDATE statement supports a JOIN clause, which opens the door to updating rows in one table based on matching data in another table. That is particularly useful for updating data across related tables.
Note that this operation should not be confused with a multi-table UPDATE, where multiple tables are updated simultaneously. In a MySQL UPDATE JOIN, data from multiple tables is used to determine the updates, but only rows in a single table are actually modified.
Scenarios of Using an UPDATE with JOIN in MySQL
Using an UPDATE with a JOIN in MySQL is useful in all high-level scenarios where you need to update rows in a table based on information from another related table.
For instance, consider an e-commerce platform where you have an orders table and a customers table. If you want to update the shipping status of orders based on whether the customer is a premium member, you would have to use a JOIN to fetch the relevant data from both tables and update the orders table accordingly. Another example could involve synchronizing prices in a product catalog based on discounts from a promotions table.
In both cases, a MySQL UPDATE JOIN query enables you to efficiently update rows in one table using data from another. Read our guide for more information on how JOIN operations work.
How to Perform a MySQL UPDATE with JOIN
The syntax of a query with a JOIN in an UPDATE statement in MySQL is as follows:
1
UPDATE table_1 T1
2
[INNER | LEFT] JOIN table_2 T2 ON T1.foreign_id = T2.id
3
SET T1.column_1 = T2.column_1
4
[WHERE ...];
The UPDATE JOIN query in MySQL updates rows in table_1 using data from table_2 based on the matching condition specified in the JOIN clause. In the case of an INNER JOIN, only rows in table_1 that have matching rows in table_2 are updated. With a LEFT JOIN, all rows in table_1 are updated; for rows that do not have a match in table2, NULL values are assigned to the columns in table_2.
Keep in mind that the WHERE clause at the end of the query is essential to prevent all matching rows from being updated. Thanks to it, only the selected joined rows in the filter will be updated.
Notes
Both RIGHT JOIN and CROSS JOIN are technically supported in UPDATE queries in MySQL, but their usage might not always make sense:
That is why RIGHT and CROSS joins were not mentioned in the above MySQL UPDATE JOIN syntax. Now that you know the reason behind that, let’s move on to the examples of the UPDATE JOIN clause!
MySQL UPDATE JOIN: Examples
Now that you know how UPDATE with JOIN works in MySQL, you are ready to see it in action in two real-world examples.
Note: DbVisualizer, a top-rated and powerful MySQL database client, will be used in this section to make query execution and data exploration easier.
We will update data in the employees and departments sample tables. This is what the employees table looks like:
And here is the departments table:
Note that department_id in the employees table is a foreign key referencing the departments table, as you can see in the ERD-like references graph generated by DbVisualizer:
Now, let's join and update some data!
UPDATE INNER JOIN Example
Suppose you want to give all employees in the "Engineering" department a 10% salary raise. First, you need to retrieve all employees in that department. You can achieve that with the following INNER JOIN query:
1
SELECT *
2
FROM employees E
3
INNER JOIN departments D ON E.department_id = D.id
4
WHERE D.name = 'Engineering';
The result will be:
You can now update their salary with the MySQL UPDATE with JOIN below:
1
UPDATE employees E
2
INNER JOIN departments D ON E.department_id = D.id
3
SET E.salary = E.salary * 1.10
4
WHERE D.name = 'Engineering';
If you repeat the INNER JOIN query, you will now see:
Note how the salaries have increased as desired.
UPDATE LEFT JOIN Example
Assume you want to give a 5% salary increase to all employees who either do not have a department or whose department is “Sales.” To achieve that, you should use a LEFT JOIN query:
1
SELECT *
2
FROM employees E
3
LEFT JOIN departments D ON E.department_id = D.id
4
WHERE E.department_id IS NULL OR D.name = "Sales";
Execute it, and you will get:
You can now perform an update query with:
1
UPDATE employees E
2
LEFT JOIN departments D ON E.department_id = D.id
3
SET E.salary = E.salary * 1.05
4
WHERE E.department_id IS NULL OR D.name = "Sales";
The affected rows will now contain:
How NULL Values Impact Your JOIN Operations
Lastly, keep in mind that NULL values have an impact on JOIN operations, too. Remember—we have assumed that we give a 5% salary increase to all employees who either do not have a department or whose department is “Sales.”
That sentence means that some of you may elect to store NULL values as part of a column value, while some may opt for an empty value in the column. Both approaches are good—but if you use NULL values as part of your JOIN operations, it is important to keep in mind the following:
Keep the above mentioned points in mind, et voilà! You will soon be a MySQL UPDATE JOIN master.
Conclusion
In this blog post, you learned how to use UPDATE statements with a JOIN clause. As demonstrated in two examples, the process becomes much easier with a feature-rich visual database client like DbVisualizer.
DbVisualizer offers many features, including visual query execution, data exploration, and table discovery. Additionally, it comes with advanced features like SQL formatting and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is it possible to perform a MySQL INNER JOIN UPDATE query?
Yes, performing an INNER JOIN in a MySQL UPDATE query is possible. This allows you to update values in one table based on matching rows from another table. The INNER JOIN ensures that only rows with matching conditions in both tables are included in the update.
Is it possible to join 3 tables in an UPDATE query in MySQL?
Yes, you can join 3 or more tables in a MySQL UPDATE query. You can chain multiple JOIN clauses together to combine data from multiple tables before updating the target table. That gives you the ability to update values based on conditions from all tables in the JOIN clause.
Is it possible to write a MySQL UPDATE JOIN WHERE query?
Yes, this is not only possible, but a WHERE clause in a MySQL UPDATE JOIN query helps specify the conditions under which rows should be updated after joining tables. This ensures that only the rows meeting the criteria in the WHERE clause are updated, preventing the update from affecting all rows that match the JOIN condition.
Is RIGHT JOIN supported in MySQL UPDATE queries?
Yes, RIGHT JOIN is supported in MySQL UPDATE queries, but its use is uncommon. Rewriting the query using a LEFT JOIN is typically easier and more common.
Why use a database client to deal with JOIN queries?
DbVisualizer is a robust, highly-rated multi-database client that allows you to manage various databases from a single platform. One of its standout features is the ability to visually display data from queries and show the relationships between tables in a database. This simplifies the understanding of JOIN queries and enhances the overall experience of working with relational data. Grab a 21-day DbVisualizer free trial!
