title: Update Statement with subquery
body: <h2>MS Access Update Statement with subquery</h2>
  
  <p>I have Order table and OrderDetails table. OrderDetails table has orderline items with quantity and subtotal. I would like to run a query to update "Total Amount Before Discount" in Order table with the sum value of correspondent orderline subtotals.</p>
  
  <p>The query I have now is below. I couldn't run it in Access. It tells me "you have written a subquery that can return more than one field without using the EXISTS reserved word in the main queries FROM clause. revise the SELECT statement of the subquery to request only one field."</p>
  
  <pre><code>UPDATE [Order] INNER JOIN OrderDetails ON Order.ID = OrderDetails.[Order ID]
  SET [Order].[Total Amount Before Discount] = 
      (SELECT Order.ID, Sum(OrderDetails.[Subtotal After Discount]) AS [SumOfSubtotal After Discount]
       FROM [Order] INNER JOIN OrderDetails ON Order.ID = OrderDetails.[Order ID]
       GROUP BY Order.ID)
       WHERE (((Order.ID)=[OrderDetails].[Order ID]));
  </code></pre>