Search code examples
mysqljoinsql-updateinner-join

MySQL - Update/Set a column in one table equal to MAX value from another table

I've found a number of hits for what I'm trying to do, but only when doing a SELECT and I haven't been able to get those methods to work with an UPDATE.

Table: Project
Site | I-Date | Status

Table: Schedule
Site | F-Date | Status

Here's the query I have now, but I want to guarantee that the project date is set only to the MAX date as there could be multiple entries for each site, but with different dates in schedule.

UPDATE project
    INNER JOIN schedule on project.site = schedule.site 
    SET project.i-date = schedule.f-date, project.status = 'complete'
    WHERE project.site = 'site123'

I tried doing this:

UPDATE project
    INNER JOIN schedule on project.site = schedule.site 
    SET project.i-date = MAX(schedule.f-date) as 'f-date', project.status = 'complete'
    WHERE project.site = 'site123'

but that didn't work. I suspect I need some different methods of joining but having trouble getting the right mixture.

Solution

  • How about a subselect?

    UPDATE project
SET 
    project.`i-date` = (
        SELECT MAX(`f-date`)
        FROM schedule
        WHERE schedule.site = project.site
    ),
    project.status = 'complete'
WHERE project.site = 'site123'