title: How can i rectify the below error (Subquery returns more than 1 row) in mysql
body: <p>I have five tables such as tb_rawmaterial,tb_ingredients,tb_food, tb_order_items, tb_stock.</p>
  
  <p>tb_rawmaterial has all the rawmaterial for a food </p>
  
  <p>tb_ingredients connects the tb_food and the tb_rawmaterial with food_id as foreign key</p>
  
  <p>tb_orderitems connects the tb_foods with food_id as foreign key.</p>
  
  <p>tb_stock has all the rawmaterial stock level details it connects tb_rawmaterial with material_id as foreign key.</p>
  
  <p>now what i have to do is :</p>
  
  <p>i have to write an update query to change the stock level when make an order for a food, i wrote the below query:</p>
  
  <pre><code>UPDATE tb_stock set tb_stock.quantity = (tb_stock.quantity-(
      SELECT tb_ingredients.usage_volume * sum(tb_order_item.qty)
      FROM tb_ingredients 
      INNER JOIN tb_order_item 
      ON(tb_order_item.food_id=tb_ingredients.food_id
      AND tb_order_item.order_id='ORD-T01-00289') 
      GROUP BY tb_ingredients.food_id )) 
      WHERE material_id=(SELECT tb_ingredients.material_id
      FROM tb_ingredients 
  INNER JOIN tb_order_item 
  ON(tb_order_item.food_id=tb_ingredients.food_id
  AND tb_order_item.order_id='ORD-T01-00289')
  GROUP BY tb_ingredients.food_id)
  </code></pre>
  
  <p><strong>BUT</strong></p>
  
  <p>it works if the raw material data is only one, if the same raw material data repeat again it gives the below error:</p>
  
  <p><strong>Subquery returns more than 1 row</strong></p>
  
  <p>How can i rectify this?
  Please advice me</p>