Show you how to perform cross-table updates using the UPDATE JOIN statement with INNER JOIN and LEFT JOIN.
Introduction
In MySQL, you can update records in one table based on data from another table using the UPDATE JOIN statement. This is helpful when you want to modify data across related tables.
What is the MySQL UPDATE JOIN Statement?
- The UPDATE JOIN allows you to update data in one table (T1) based on values in another related table (T2).
- You can use either INNER JOIN or LEFT JOIN depending on how you want the rows to match between the two tables.
Syntax for UPDATE with INNER JOIN
UPDATE table1
INNER JOIN table2 ON table1.column1 = table2.column1
SET table1.column2 = table2.column2,
table2.column3 = expression
WHERE condition;Explanation of INNER JOIN
- INNER JOIN: This will update only the rows in table T1 that have a matching row in table2 based on the condition in the
ONclause (table1.column1 = table2.column1). - Usage: Use this when you want to update data only where the rows in both tables match.
Example of UPDATE with INNER JOIN
Let’s say you have two tables:
employees: Contains employee details.departments: Contains department information.
Let's represent the two tables, employees and departments, to illustrate the example:
1. employees Table:
employee_id | emp_name | dept_id | job_title | department_name |
1 | Alice Smith | 101 | Manager | NULL |
2 | Bob Johnson | 102 | Developer | NULL |
3 | Carol White | 103 | Manager | NULL |
4 | David Brown | 101 | Analyst | NULL |
5 | Eve Davis | 102 | Manager | NULL |
2. departments Table:
dept_id | dept_name |
101 | HR |
102 | IT |
103 | Finance |
You want to update the department name in the employees table based on matching department information in the departments table.
UPDATE employees
INNER JOIN departments ON employees.dept_id = departments.dept_id
SET employees.department_name = departments.dept_name
WHERE employees.job_title = 'Manager';The query aims to update the department_name column in the employees table based on matching dept_id values from the departments table, but only for employees whose job_title is 'Manager'. So, after the UPDATE statement runs, only those employees who are 'Managers' will have their department_name updated.
Result After Running the Query:
employee_id | emp_name | dept_id | job_title | department_name |
1 | Alice Smith | 101 | Manager | HR |
2 | Bob Johnson | 102 | Developer | NULL |
3 | Carol White | 103 | Manager | Finance |
4 | David Brown | 101 | Analyst | NULL |
5 | Eve Davis | 102 | Manager | IT |
What Happened:
- Alice Smith (dept_id 101, Manager): Updated to "HR".
- Carol White (dept_id 103, Manager): Updated to "Finance".
- Eve Davis (dept_id 102, Manager): Updated to "IT".
Only the employees with the job_title as "Manager" had their department_name updated based on their dept_id.
Syntax for UPDATE with LEFT JOIN
UPDATE table1
LEFT JOIN table2 ON table1.column1 = table2.column1
SET table1.column2 = table2.column2,
table2.column3 = expression
WHERE condition;Explanation of LEFT JOIN
- LEFT JOIN: This will update all rows in table T1, even if there is no matching row in table T2. If there is no match, the columns from T2 will be
NULL. - Usage: Use this when you want to update all rows from T1, even if there’s no corresponding data in T2.
Here’s a table representation for the employees and departments tables to illustrate the LEFT JOIN query.
1. employees Table (Before the Update):
employee_id | emp_name | dept_id | job_title | department_name |
1 | John Smith | 101 | Sales rep | NULL |
2 | Jane Doe | 102 | Manager | NULL |
3 | Bob Johnson | 103 | Sales rep | NULL |
4 | Alice Brown | 104 | Analyst | NULL |
5 | Charlie Black | 101 | Sales rep | NULL |
2. departments Table:
dept_id | dept_name |
101 | Sales |
102 | HR |
103 | IT |
105 | Marketing |
UPDATE employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id
SET employees.department_name = departments.dept_name
WHERE employees.job_title = 'Sales rep';Explanation of the Query:
- The query updates the
department_namecolumn in theemployeestable based on a matchingdept_idin thedepartmentstable. - Only employees with the
job_title= 'Sales rep' will have theirdepartment_nameupdated. - This is a
LEFT JOIN, meaning all employees will be considered, even if there's no match in thedepartmentstable.
1. employees Table (After the Update):
employee_id | emp_name | dept_id | job_title | department_name |
1 | John Smith | 101 | Sales rep | Sales |
2 | Jane Doe | 102 | Manager | NULL |
3 | Bob Johnson | 103 | Sales rep | IT |
4 | Alice Brown | 104 | Analyst | NULL |
5 | Charlie Black | 101 | Sales rep | Sales |
What Happened:
- John Smith (dept_id 101, Sales rep) was updated to "Sales".
- Bob Johnson (dept_id 103, Sales rep) was updated to "IT".
- Charlie Black (dept_id 101, Sales rep) was updated to "Sales".
- No changes were made to employees who are not "Sales reps", such as Jane Doe (Manager) and Alice Brown(Analyst).
Differences Between INNER JOIN and LEFT JOIN in UPDATE
INNER JOIN | LEFT JOIN |
Only updates rows where table1 has a matching row in table2. | Updates all rows from table1, even if table2 has no match. |
Rows without a match in table2 are not updated. | Rows without a match in table2 will have values from table2as NULL. |
Use when you only want to update matching rows. | Use when you need to update all rows in table1, regardless of matching rows in table2. |
Key Points to Remember
- INNER JOIN: Updates rows where there is a matching row in both
table1andtable2. - LEFT JOIN: Updates all rows from
table1, even if no matching row exists intable2(with unmatched rows fromtable2beingNULL). - SET Clause: Specifies which columns you want to update and what their new values should be.
- WHERE Clause: Filters which rows should be updated.