Skip to content
Advertisement

How to UPDATE a field from a SELECT table using JOIN

I have three tables.

inspectionreport

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

inspection

inspection has InspectionID PK and PartID_id FK.

part

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:

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.

Advertisement

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:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement