title: Complicated MySql Update Join Query
body: <p>Have is an example of the problem I'm facing. The database tables are a little different than usual, but needed to be setup this way.</p>
  
  <p>Items: id, order_id, other fields</p>
  
  <p>Items_Drinks: id, drinks, other fields</p>
  
  <p>Orders: id, other fields</p>
  
  <p>Orders_Drinks: id, drinks, other fields</p>
  
  <p>I need to have an update query that will update the Orders_Drinks table with the sum of the Items_Drinks drinks field that have the same order_id as Orders_Drinks id field.</p>
  
  <pre><code>Items: 1   1   ...
  Items: 2   1   ...
  Items_Drinks: 1   4   ...
  Items_Drinks: 2   5   ...
  
  Orders: 1   ...
  Orders_Drinks: 1   9   ...
  </code></pre>
  
  <p>The Orders_Drinks is currently correct, but if I were to update Items_Drinks with id of 1 to 5, I would need an update command to get Orders_Drinks with id 1 to equal 10.</p>
  
  <p>It would be best if the command would update every record of the Orders_Drinks. </p>
  
  <p>I know my database is not typical, but it is needed for my application. This is because the Drinks table is not needed for all entries. The Drinks table has over 5000 fields in it, so if every record had these details the database would grow and slow for no real reason. Please do not tell me to restructure the database, this is needed. </p>
  
  <p>I am currently using for loops in my C# program to do what I need, but having 1 command would save a ton of time! </p>
  
  <p>Here is my best attempt, but it gives an error of "invalid group function".</p>
  
  <pre><code>update Orders_Drinks join Items on Items.order_id=Orders_Drinks.id join Items_Drinks on Items_Drinks.id=Items.id set Orders_Drinks.drinks=sum(Item_Drinks.drinks);
  </code></pre>