Search

8. UPDATE JOIN 

8. UPDATE JOIN 

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 ON clause (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_name column in the employees table based on a matching dept_id in the departments table.
  • Only employees with the job_title = 'Sales rep' will have their department_name updated.
  • This is a LEFT JOIN, meaning all employees will be considered, even if there's no match in the departments table.

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

  1. INNER JOIN: Updates rows where there is a matching row in both table1 and table2.
  2. LEFT JOIN: Updates all rows from table1, even if no matching row exists in table2 (with unmatched rows from table2 being NULL).
  3. SET Clause: Specifies which columns you want to update and what their new values should be.
  4. WHERE Clause: Filters which rows should be updated.