title: MySQL UPDATE with SELECT SUM from different table
body: <p>I have two tables:
  <code>ITEMS</code> with quantities and unit_price (id | name | order_id |  qt | unit_price)
  and table <code>ORDERS</code>.</p>
  
  <p>I want to <code>UPDATE</code> table <code>orders</code> and place in <code>orders.total_price sum of multiplications qt*unit_price</code> for the same orders to get total price of the order.</p>
  
  <p>The <code>SELECT</code> query on the items table is quite simple and works fine giving sums for all items within the same order_id:</p>
  
  <pre><code>SELECT SUM(items.qt*items.unit_price) from items GROUP by items.order_id
  </code></pre>
  
  <p>but I can't insert this value in my <code>ORDERS</code> table. I couldn't make this work:</p>
  
  <pre><code>UPDATE orders, items SET orders.total_price = (SELECT SUM(items.qt*items.unit_price)
  FROM items GROUP BY items.order_id) WHERE orders.id = items.order_id
  </code></pre>
  
  <p>it returns <code>"Subquery returns more than 1 row"</code></p>
  
  <p>I found a very similar question <a href="https://stackoverflow.com/questions/4212479/mysql-update-with-table-joins-update-one-tables-field-with-sum-of-other-table">here</a> but the answer didn't work for me as well:</p>
  
  <pre><code>UPDATE orders SET orders.t_price = (SELECT SUM(items.qt*items.unit_price) from items WHERE orders.id = items.order_id)
  </code></pre>