MySQL Update with Inner Join

This article will look into how to update rows to a MySQL table using the INNER JOINs. INNER JOINs help us retrieve records from more than one table having a condition satisfied by both and are logically related to each other.

Let us get started by making the sample data. We will be creating two tables, employee_designation and employee_rating, followed by inserting a few rows into them.

#create table employee_designation
CREATE TABLE employee_designation
(
  id int NOT NULL AUTO_INCREMENT,
  emp_id int DEFAULT NULL,
  emp_name varchar(255) DEFAULT NULL,
  emp_designation varchar(255) DEFAULT NULL,
  primary key(id)
);
# insert rows into employee_designation
INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
 VALUES(1,"Henry","Assistant Manager"),
 (2,"Richa","Executive"),
 (3,"Veronica","Executive"),
 (4,"George","Executive"),
 (5,"Harsh","Assistant Manager"),
 (6,"William","Assistant Manager"),
 (7,"Rebecca","Senior Manager"),
 (8,"Paul","Assistant Manager"),
 (9,"Pualou","Executive");
#create table employee_rating
 CREATE TABLE employee_rating
(
   id int AUTO_INCREMENT,
   emp_id int,
   emp_name VARCHAR(255),
   emp_rating int,
   primary key(id)
);
#insert rows to table employee_rating
INSERT INTO employee_rating (emp_id,emp_rating) 
 VALUES(1,1),
 (2,3),
 (3,4),
 (4,1),
 (5,2),
 (6,2),
 (7,2),
 (8,2),
 (9,1);

Execute select * to view the data in employee_designation, employee_rating table.

SELECT * FROM employee_designation;

Output:-

SELECT * FROM employee_rating;

Output:-

Note: in image_2, emp_name is missing in the employee_rating table. 

Example1: Update records in employee_rating table such that emp_name values get pulled from employee_designation table.

Observe the below code for the solution.

 UPDATE employee_rating AS r
        INNER JOIN
    employee_designation d ON r.emp_id = d.emp_id 
SET 
    r.emp_name = d.emp_name;

Action Output:-

To verify if the update was successful in the employee_rating table, let us execute:

 SELECT * FROM employee_rating;

Output:-

Output in image_4 shows that all the rows are updated, and the names are now present in the emp_name column of employee_rating table.

Explanation:-

• The above update query fetches the rows in the employee_rating table with common emp_id in both employee_designation and employee_rating tables using the INNER JOIN.
• Finally, these rows get updated in the employee_rating table with the emp_name from employee_designation.

READ MORE:

• MySQL Insert with Join
• MySQL: Insert in a loop
• INSERT record if NOT EXISTS in MySQL
• Insert into a MySQL table or update if exists
• MYSQL INSERT INTO
• MYSQL INSERT WITH SELECT

We hope this article helped you in understanding MySQL UPDATE with INNER JOIN. Good Luck!!!