title: Update with SUM()
body: <p>I have a table structure similar to the ones below</p>
  
  <pre><code>Table1
  
  BatchID     Quantity     ProductID
  1           10           1
  
  1           10           1
  
  1           20           2
  </code></pre>
  
  <p>I need to group by Product ID and get the max quantity and then sum that up and update table2. So the result in table2 would be</p>
  
  <pre><code>Table2
  
  BatchID    Total
  1          30
  </code></pre>
  
  <p>The update query i came up with was</p>
  
  <pre><code>UPDATE T2
  SET
  Table1.Total = Sum(sub.quantity)
  FROM Table2 T2
  INNER JOIN 
      (SELECT 
          MAX(T1.Quantity) quantity,
      FROM Table1 T1
      GROUP BY T1.ProductID,Ti.Quantity) AS sub ON sub.BatchID= T1.BatchID
  WHERE T1.BatchID = 1
  </code></pre>
  
  <p>When i run this query it throws this error
  "An aggregate may not appear in the set list of an UPDATE statement."</p>
  
  <p>Please help me fix this error. </p>