Mysql Update query with Join and Group By


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

Syntax 
UPDATE 
  table1 t1 
SET 
  t1.field2 =(
    SELECT 
      MAX(t2.field3) 
    FROM 
      table2 t2 
    WHERE 
      t2.field1 = t1.field1 
    GROUP BY 
      t2.field3
  );

For example, take the following two tables.

enquiry

MySQL Update query with Join and Group By

services

MySQL Update query with Join and Group By

In this case, we need to update the field (last_update_date) in enquiry table from the last service date of the field (service_date) in services table. The following query helps to solve this case.

Example 
UPDATE
  enquiry t1
SET 
  t1.last_update_date =(
    SELECT 
      MAX(t2.service_date)
    FROM 
      services t2
    WHERE 
      t2.enq_no = t1.enq_no
    GROUP BY 
      t2.enq_no
  );

The query result is

MySQL Update query with Join and Group By