title: Update multiple rows in a table based on result from subquery
body: <p>I have the following query:</p>
  
  <pre class="lang-mysql prettyprint-override"><code>SELECT 
    stat.mcq_id, 
    ROUND( stat.total_score / stat.num_taken, 2 ) AS avg_score
      FROM (
        SELECT 
          user_mcq.mcq_id, 
          SUM( score ) AS total_score, 
          COUNT( user_mcq.id ) AS num_taken
        FROM user_mcq
        INNER JOIN user ON ( user.id = user_mcq.user_id ) 
        WHERE user.level_id =3
        AND user_mcq.is_complete =1
        GROUP BY user_mcq.mcq_id
      ) AS stat
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code>mcq_id  avg_score
  1       5.75
  2       9.22
  6       8.81
  7       8.94
  14      7.00
  16      9.46
  </code></pre>
  
  <p>I would like to use this to update another table called <code>mcq</code> using <code>mcq_id</code> from the result to match against <code>mcq.id</code></p>
  
  <p>I've tried the following, but without success:</p>
  
  <pre class="lang-mysql prettyprint-override"><code>UPDATE mcq SET mcq.avg_score = stats.avg_score FROM (
    SELECT 
    stat.mcq_id, 
    ROUND( stat.total_score / stat.num_taken, 2 ) AS avg_score
      FROM (
        SELECT 
          user_mcq.mcq_id, 
          SUM( score ) AS total_score, 
          COUNT( user_mcq.id ) AS num_taken
        FROM user_mcq
        INNER JOIN user ON ( user.id = user_mcq.user_id ) 
        WHERE user.level_id =3
        AND user_mcq.is_complete =1
        GROUP BY user_mcq.mcq_id
      ) AS stat
  ) AS stats
  WHERE mcq.id = stats.mcq_id;
  </code></pre>
  
  <p>This gives: </p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ( SELECT stat.mcq_id, ROUND( stat.total_score / stat.num_taken, 2 ) A' at line 1
  </code></pre>