UPDATE with INNER JOIN - MySQL

The following example shows, how to use the UPDATE query with INNER JOIN in MySQL.

Syntax

UPDATE 
  table1 t1 
  INNER JOIN table2 t2 ON t1.field1 = t2.field1 
SET 
  t1.field2 = t2.field3 
WHERE 
  t1.field2 > 0 
  AND t2.field3 > 0;

For example, take the following two tables.

employee

salary

In this case, we need to update the field (net_salary) in employee table from the field (salary_per_day) in salary table based on their working days (working_days). The following query helps to solve this case.

Example 1

UPDATE 
  employee t1 
  INNER JOIN salary t2 on t1.category = t2.category 
SET 
  t1.net_salary = (t2.salary_per_day * t1.working_days)

The query result is

The following example shows, how to use WHERE in UPDATE JOIN query.

Example 2 - with WHERE

UPDATE 
  employee t1 
  INNER JOIN salary t2 on t1.category = t2.category 
SET 
  t1.net_salary = (t2.salary_per_day * t1.working_days) 
WHERE 
  t1.working_days > 0