Summary -

In this topic, we described about the UPDATE JOIN with detailed example.

UPDATE JOIN statement is used to update the one table with another table values by using JOIN condition.

Syntax -

UPDATE table1 SET table1_column = table2_column
FROM table1 INNER | LEFT JOIN table2
ON table1.column = table2.column
[WHERE condition]; 

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id dname
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Update the values in table by using UPDATE JOIN.

Requirement1 – Update the dname on employee_details with dept_name from department_details by using INNER JOIN condition. The query was as follows –

UPDATE employee_details
SET  dname = department_details.dept_name
FROM employee_details INNER JOIN department_details ON
employee_details.dept_id = department_details.dept_id; 

BY executing above query, we can update the dname in employee_details table with dept_name of department_details table by using INNER JOIN condition. The table after update was as follows-

emp_id emp_name designation manager_id date_of_hire salary dept_id dname
001 Employee1 Director 2019-07-11 45000.00 1000 Office
002 Employee2 Director 2019-07-11 40000.00 2000 Sales
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000 Office
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000 Sales
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000 Office
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000 Office
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000 Office
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000 Sales
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000 Sales

Scenario2 - Update the values in table by using UPDATE JOIN.

Let us consider the employee_details and department_details tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id dname
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee10 Salesman Employee3 2019-09-09 15000.00 3000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Requirement – Updating the manager_id on employee_details with dept_name on department_details by using LEFT JOIN condition. The query was as follows –

UPDATE employee_details SET dname = department_details.dept_name
FROM employee_details LEFT JOIN department_details ON
employee_details.dept_id = department_details.dept_id; 

BY executing above query, we can update the manager id in employee_details table with dept_name of department_details table by using LEFT JOIN condition. The table after update was as follows-

emp_id emp_name designation manager_id date_of_hire salary dept_id dname
001 Employee1 Director 2019-07-11 45000.00 1000 Office
002 Employee2 Director 2019-07-11 40000.00 2000 Sales
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000 Office
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000 Sales
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000 Office
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000 Office
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000 Office
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000 Sales
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000 Sales
010 Employee10 Salesman Employee3 2019-09-09 15000.00 3000