title: MySQL/SQL: Update with correlated subquery from the updated table itself
body: <p>I have a generic question that I will try to explain using an example.</p>
  
  <p>Say I have a table with the fields: "id", "name", "category", "appearances" and "ratio"</p>
  
  <p>The idea is that I have several items, each related to a single category and "appears" several times. The ratio field should include the percentage of each item's appearances out of the total number of appearances of items in the category.</p>
  
  <p>In pseudo-code what I need is the following: </p>
  
  <ul>
  <li><p><strong>For each category</strong><br>
  find the total sum of appearances for items related to it. For example it can be done with (<code>select sum("appearances") from table group by category</code>)</p></li>
  <li><p><strong>For each item</strong><br>
  set the ratio value as the item's appearances divided by the sum found for the category above</p></li>
  </ul>
  
  <p>Now I'm trying to achieve this with a single update query, but can't seem to do it. What I thought I should do is:</p>
  
  <pre><code>update Table T    
  set T.ratio = T.appearances /   
  (    
  select sum(S.appearances)    
  from Table S    
  where S.id = T.id    
  )
  </code></pre>
  
  <p>But MySQL does not accept the alias T in the update column, and I did not find other ways of achieving this. </p>
  
  <p>Any ideas?</p>