MySQL - Update/Set a column in one table equal to MAX value from another table
<p>I've found a number of hits for what I'm trying to do, but only when doing a <code>SELECT</code> and I haven't been able to get those methods to work with an <code>UPDATE</code>.</p>

<pre><code>Table: Project
Site | I-Date | Status

Table: Schedule
Site | F-Date | Status
</code></pre>

<p>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 <code>schedule</code>.</p>

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

<p>I tried doing this:</p>

<pre><code>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'
</code></pre>

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