title: MySQL - Update/Set a column in one table equal to MAX value from another table
body: <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>