MySQL UPDATE JOIN – A Quick Guide

In this tutorial, we will learn about the UPDATE JOIN statement. The UPDATE JOIN statement is basically used to update the table data based on the joins. We will see how to perform the update join operation using the left join as well as the inner join. So, let’s get started!

Also read: MySQL FULL JOIN [With Easy Examples]

Introduction to the UPDATE JOIN

The UPDATE JOIN statement is used to perform the cross-table updating of the records.

Let’s make it easier. We use the join statements to find the records which get matched in both tables. So, if you want to update the data in one table that is somehow related to another table, we use the UPDATE JOIN statement.

I hope it is clear now about the UPDATE JOIN statement. Now let’s see the syntax of the UPDATE JOIN statement.

MySQL UPDATE JOIN Syntax

The syntax of the UPDATE JOIN is following-

UPDATE table1, [table2,]
[INNER JOIN | LEFT JOIN] table1 ON table1.col = table2. col
SET table1.col = val
WHERE conditionCode language: SQL (Structured Query Language) (sql)

Here,

Table1 is the main table and table2 is the table that you want to join with table1. Note that, you must specify at least one table after the update clause.

You can choose the UPDATE JOIN on either the INNER JOIN or the LEFT JOIN.

Then you set the new values that you want to update with the where condition to find the particular data. If you want to update all records, you can skip the where condition.

Now let’s see some examples to understand how the update join statement works.

MySQL UPDATE JOIN Practical Example

Here, we will need two tables that are related to each other using a foreign key.

We will create two tables to store the employee salary data and levels.

The levels table will store the different levels and the bonus percentage. On the other hand, the emps table will store the names, their levels, and the salary details.

Creating a Sample Table With Join

Let’s create tables and insert data into them.

--- levels table
CREATE TABLE levels(
    lev INT PRIMARY KEY,
    bonusPercentage FLOAT NOT NULL
);


--- emps table
CREATE TABLE emps(
    empid INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    lev INT NOT NULL,
    salary FLOAT NOT NULL,
    FOREIGN KEY (lev) REFERENCES levels(lev)
);


--- levels table data
INSERT INTO levels(lev,bonusPercentage)
VALUES(1,3),(2,5),(3,6),(4,9),(5,10);


--- emps table data
INSERT INTO emps(name,lev,salary)
VALUES('John',5,40000),('Mary',1,20000),('Peter',3,30000),('Jane',1,14000),('Tom',5,50000),
('Jack',2,25000),('Bob',4,37000),('Alice',3,32000),('Sam',2,19000),('Tim',4,30000);Code language: SQL (Structured Query Language) (sql)

Perfect!

Using the Update Join Function

Now we will see an example to use the UPDATE JOIN with the INNER JOIN clause.

Here, we will update the salaries of all employees by adding bonuses according to their levels. Note that, the levels are stored in the levels table, so we have to use the inner join clause.

UPDATE emps
INNER JOIN levels
ON emps.lev = levels.lev
SET salary= salary + (salary*bonusPercentage/100);Code language: SQL (Structured Query Language) (sql)

Now let’s execute the above query and see the result.

As you can see, all salaries are updated according to the employees’ levels.

Here, the query finds the value of the lev column in both tables. If a match happens, it will update the value in the main table.

Updating Joins When Foreign Key is Missing

But what if the foreign key is missing in the main table? That means you can not use the inner join here.

In this case, we are going to use the left join and update the value as per our needs.

But before that, we have to remove the NOT NULL constraint from the lev column of the emps table.

--- modify the column
ALTER TABLE emps MODIFY column lev INT NULL;

--- inserting new values
INSERT INTO emps(name,lev,salary) VALUES('Xiaoming',NULL,20000),('Nancy',NULL,30000);Code language: SQL (Structured Query Language) (sql)

As you can see, we have inserted two new records that contain the NULL values for the level column.

Now, to update the salaries, we can not use the inner join because we don’t have the level column values for the newly inserted records.

Therefore, we will use the left join. Here, we will simply add the 5% bonus to the newly added employees.

Let’s see.

UPDATE emps 
LEFT JOIN levels ON emps.lev=levels.lev 
SET salary=salary+salary*0.05 WHERE emps.lev IS NULL;Code language: SQL (Structured Query Language) (sql)

Let’s execute the query and see the result.

As you can see, the records are updated successfully. We have used the IS NULL operator to find the records which have null values in the lev column to update the records.

Summary

In this tutorial, we have learned how to use the UPDATE JOIN statement with the INNER JOIN as well as the LEFT JOIN clauses. I hope you have understood the topic and enjoyed it as well while learning it. You can try using the UPDATE JOIN clause in different scenarios in your projects for better understanding.