title: Mysql Nested sub queries unknown column error
body: <p>i have a pretty big query with 2 sub queries. My big query updates an entire column E.
  The table looks like this:</p>
  
  <pre><code>--------------------------
  id  | A      | B    | E  |
  --------------------------
  1   |  NULL  | NULL |NULL|
  --------------------------
  2   |  4     | 6    |NULL|
  --------------------------
  3   |  6     | 9    |NULL|
  --------------------------
  </code></pre>
  
  <p>This is my entire query below:</p>
  
  <pre><code> Update mydatabase.mytable  ,
      (SELECT t1.A/AVG(t2.A)
      FROM    mytable t1
      JOIN    mytable t2
      ON      t2.id &lt;= t1.id
      group by t1.id ) AS C,
      (SELECT t1.B/AVG(t2.B)
      FROM    mytable t1
      JOIN    mytable t2
      ON      t2.id &lt;= t1.id
      group by t1.id ) AS D
      SET E = (C+D)/2;
  </code></pre>
  
  <p>I get the error : <code>Unknown column 'C' in 'field list'</code></p>
  
  <p>believe i will also get the same error for D.</p>
  
  <p>My subqueries work and the syntax is correct. i am just unsure of the big query and the update part.</p>
  
  <p>Thanks,</p>
  
  <hr>
  
  <p><strong>EDIT:</strong>
  How can i edit the <strong>ON clause part</strong> of the query above such that i would like the current code to work where id&lt;=14 (which is t2.id &lt;= t1.id as shown above) so when t1 id =14, t2 is all the cumulative id from 1 to 14 as it is now. </p>
  
  <p><strong>but</strong> for id >14 I would like the ON clause to be <strong><code>(t2.id=t1.id&gt;=t1.id-2 and &lt;=t1.id)</code></strong>
  so when t1 id=15, t2.id should be between 13 and 15.
  when t1 id =16, t2.id should be between 14 and 16 and so on. </p>
  
  <p>This is because when i calculate col E for ids after id=14, i am only interested in getting the average of the previous 2 rows for C and D on a moving average. </p>