How to UPDATE a field from a SELECT table using JOIN

I have three tables.

inspectionreport

+----------+-----------------+------+
| ReportID | InspectionID_id | Date |
+----------+-----------------+------+

inspectionreport has ReportID PK, InspectionID_id FK, and date field Date.

inspection

+--------------+------------+
| InspectionID | PartID_id  |
+--------------+------------+

inspection has InspectionID PK and PartID_id FK.

part

+--------+---------------+
| partID | LastInspected |
+--------+---------------+

part has a partID PK and date field LastInspected which is null by default.

What I want to do is fill LastInspected with THE LATEST Date for the relevant part.

So far I’ve tried using this SELECT query:

SELECT * 
FROM moorings_inspectionreport 
JOIN moorings_inspection 
     ON moorings_inspectionreport.InspectionID_id = moorings_inspection.InspectionID;

I figured I could use this to check if PartID_id matched partID as it’s now only two tables. But I’m so confused as to how to pull this off as I’m quite unfamiliar with sql queries.

I also want to put this update into a daily running event that updates for every entry in the part table. So for me that adds a whole other level of confusion.

Answer

By joining the 2 tables inspection and inspectionreport and grouping by partid you can get the last lastinspected for each partid.
Then join to part and update:

update part p
inner join (
  select i.partid, max(r.date) date
  from inspection i 
  inner join inspectionreport r on r.inspectionid = i.inspectionid
  group by i.partid
) t on p.partid = t.partid
set p.lastinspected = t.date

Advertisement

Answer