title: MySQL update column value with max value from another column
body: <p>I have a database with 2 tables:
  &quot;services&quot; and &quot;service_performance&quot;
  Those 2 tables have a SERVICE_ID column.</p>
  <p>In &quot;services&quot; the SERVICE_ID values are unique (each service has a single ID/entry).
  In &quot;service_performance&quot; there is an AVERAGE_MEMORY column with multiple entries per service_id</p>
  <p>I am trying to update the MAX_VALUE column in &quot;services&quot; table with the highest AVERAGE_MEMORY value taken from the &quot;service_performance&quot; table.</p>
  <p>I know my query is wrong because it throws an error:</p>
  <h1>1054 - Unknown column 'service_performance.SERVICE_ID' in 'where clause'</h1>
  <p>While 'service_performance.SERVICE_ID' does exist.</p>
  <p>Here is my query:</p>
  <pre><code>   update _services
  set MAX_VALUE = (SELECT MAX(AVERAGE_MEMORY) AS SERVICE_ID FROM service_performance)
  where exists 
    (select *
    from services 
    where `services`.`SERVICE_ID` = `service_performance`.`SERVICE_ID`);
  </code></pre>