title: Update multiple columns in the same statement
body: <p>Assume that I have the following database schema:
  <a href="https://i.stack.imgur.com/JefMn.png" rel="nofollow noreferrer">schema</a></p>
  
  <p>A student can have more than 2 grades in the same subject.</p>
  
  <p>I want to update the columns highest_math_grade, highest_physic_grade, highest_chemistry_grade of table student. This is how I do it:</p>
  
  <pre><code>UPDATE student JOIN (
  SELECT student.id, MAX(grade) AS grade
  FROM student join grade
  ON student.id = grade.student_id
  WHERE subject = 'math'
  GROUP BY student.id
    ) AS subquery
  ON student.id = subquery.id
  SET highest_math_grade = subquery.grade;
  
  UPDATE student JOIN (
      SELECT student.id, MAX(grade) AS grade
      FROM student join grade
      ON student.id = grade.student_id
      WHERE subject = 'physic'
      GROUP BY student.id
    ) AS subquery
  ON student.id = subquery.id
  SET highest_physic_grade = subquery.grade;
  
  
  UPDATE student JOIN (
      SELECT student.id, MAX(grade) AS grade
      FROM student join grade
      ON student.id = grade.student_id
      WHERE subject = 'chemistry'
      GROUP BY student.id
  ) AS subquery
  ON student.id = subquery.id
  SET highest_chemistry_grade = subquery.grade;
  </code></pre>
  
  <p>It is not nice that I have to use 3 update statements. I tried:</p>
  
  <pre><code>UPDATE student
    JOIN (SELECT *
          FROM grade
          ORDER BY grade) AS grade
      ON student.id = grade.student_id
  SET highest_math_grade    = (CASE WHEN grade.subject = 'math'
    THEN grade.grade
                               ELSE highest_math_grade END),
    highest_chemistry_grade = (CASE WHEN grade.subject = 'chemistry'
      THEN grade.grade
                               ELSE highest_chemistry_grade END),
    highest_physic_grade    = (CASE WHEN grade.subject = 'physic'
      THEN grade.grade
                               ELSE highest_physic_grade END);
  </code></pre>
  
  <p>However, it only updates each student once which is not what I wanted.</p>
  
  <p>Is there a way to achieve the same thing with just one update statement?</p>