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.
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.
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.
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