title: Best way to update table with values calculated from same table
body: <p>I'm totally noob in sql. I think the best way to do this; I have Receipt table with theese fields:</p>
  
  <pre><code>Receipt
  -------
  ReceiptID, AssociatedReceiptID, Value, Total
  </code></pre>
  
  <p>I want to update Total field of all rows with the same AssociatedReceiptID with the sum of their Value fields. So I've tried the next sql statement:</p>
  
  <pre><code>UPDATE Receipt r1
  SET Total = (SELECT sum(Value)
               FROM Receipt r2
               WHERE r2.AssociatedReceiptID = r1.AssociatedReceiptID
               GROUP BY r2.AssociatedReceiptID)
  </code></pre>
  
  <p>With more of 100000 records in this table, it last over 17 hours. Because of I'm updating the same table I'm quering, I decided to split it in two updates statements, storing sum result in a temporary table (which has ) and then updating Receipt table with these values.</p>
  
  <pre><code>UPDATE TemporaryTable t1
  SET Total = (SELECT sum(Value)
               FROM Receipt r2
               WHERE r2.AssociatedReceiptID = t1.AssociatedReceiptID
               GROUP BY r2.AssociatedReceiptID)
  
  UPDATE Receipt r1
  SET Total = (SELECT Total
               FROM TemporaryTable t1
               WHERE t1.ReceiptID = r1.ReceiptID)
  </code></pre>
  
  <p>Using these statements, update process takes 6-7 hours. But I'm sure there should be a better way of do this. So, in brief, these are my questions:</p>
  
  <ul>
  <li>How do yo do this in a better way?</li>
  <li>Subquery in update statements is executed once per row updated, isn't it? So, if there are 10 rows with same AssociatedReceiptID, sum is calculated 10 times. How can I calculate sum only once per AssociatedReceiptID in the update statement?</li>
  </ul>
  
  <p>Thanks in advance.</p>