MySQL UPDATE with JOIN

Syntax

The syntax for MySQL UPDATE with JOIN is:

• This syntax explains that we want to update table1 and combine it with table2 based on condition1 which should be table1.column1 = table2.column2. It specifies that the values in column1 in table1 match those in column2 in table2.
• We then set the value of table1.column1 to a value where the specified condition is met. The WHERE clause is optional in some cases and can be used to further filter the rows with condition2 as stated by the user if needed.

Introduction

Using MySQL UPDATE with JOIN, you can update the values in one table with the data in the other table by joining two tables based on a related column. UPDATE JOIN can also be used with different joins like INNER JOIN, LEFT JOIN, RIGHT JOIN, etc. Moreover, it can be used to update multiple columns of a table by adding additional SET clauses to the statement.

How does UPDATE JOIN Work in MySQL?

By using MySQL UPDATE with JOIN, we can specify the type of JOIN we need in our case. The types can vary based on whether there is the availability of the matching rows in another. For the matching rows, we can use INNER JOIN whereas LEFT JOIN can be used in the case where we know that there can be the absence of a few matching rows in the other table that is being combined.

• The table that has to be updated will be specified right after the UPDATE keyword.
• Now, we need to combine it with another table based on a matching condition. So, we'll use the JOIN keyword along with the second table name. Here, the type of JOIN statement can be chosen based on our needs.
• The ON clause specifies the condition to combine the two tables based on a standard column. This can also help join when similar columns in both tables have different names.
• You then use the SET keyword to specify the column you want to update in the first table, along with the new value you want to set.
• Finally, you can use the WHERE keyword to specify any additional conditions for the update, such as filtering rows based on certain criteria.

The UPDATE JOIN statement causes MySQL to first perform the join operation, combining rows from the two tables according to the join condition. The specified column will subsequently be set to the supplied value in the first table's rows that match the join condition.

MySQL UPDATE with JOIN Examples

Assume a scenario where there are several departments in an organization including some employees in each. Considering the performance of the employees in the Marketing team, the organization has decided to increase their salary by 10% and their bonus by 5%.

Here's the query to create the tables, i.e., Departments, Employees, and Salaries. The tables Employees will have a dependency on the table Department and similarly, the Salaries table has a foreign key from the Employees table to insert the salaries of each employee.

The tables need to have some data to perform any operation. So, let's populate all these tables with some records.

So, here are the tables with their data.

Departments:

Employees:

Salaries:

Now, given below is the query to increase the salary and the bonus of the employees from the Marketing team.

In the above query, we first combined the employees with their departments based on the dept_id and then combined their outcome with the Salaries table based on the employee_id.

We can check the final result after the query has been executed. The salary, as well as the bonus for James, will be increased.

Output:

MySQL UPDATE JOIN Example with INNER JOIN Clause

We can have a table for the grocery items with the columns item_id, item_name, and price.

Another table Orders will comprise the item_id as a reference from the item_id from the Items table.

Let's insert some data for the grocery items into the Items table. Also, the quantity for all these items will be listed in the Orders table.

Items:

Orders:

Let's update the Orders table based on their prices specified in the Items table. We'll use an inner join to combine our results based on the common column in both the tables Items and Orders.

As the calculated price for all the items in the Orders table will now be updated after executing the above query, we can check it as well.

Output:

To generate the bill, here's the total price for all the items in the Orders table.

Output:

MySQL UPDATE JOIN Example with LEFT JOIN

Suppose you have two tables for Customers and Orders. Based on the orders made by the Customers, we need to update the Customers table with the status as active (if there's an order that they've made) and null if there's no order from their side.

We have the query here to create both of these tables and insert some data into them as well.

This is what both the table Customers and Orders look like. Customers

Orders:

Now, we're going to check if a customer has any order in the Order table to decide their status in the Customers table.

We can verify the status of each customer by looking at the Customers table.

Output:

Here, in our output, we have tried to update the status for each of the customers by joining it with the related column cust_id in the Customers table.

Since, the customers Jane and Arthur had one or more orders in the Orders table, their status was updated as active.