title: SQL Server : update column with value from same table based on choice of values
body: <p>I have a table that contains inventory items, where the same item can be stored in multiple places (i.e. Total qty=3, 1 stored in cabinet, and 2 in the fridge, and 0 in the pantry). Each of these have an entry type of "20".</p>
  
  <p>There is also rows with entry type of "10" that control the totals. So, in my program I see "Available" as Entry Type "10", and can view each locations quantities by viewing the Entry Type "20".</p>
  
  <p>After running some updates, I noticed that it took my "10" types to zero even if there were items in the "20" rows (on a certain set of items, which I have a list of to include within an "IN" statement to change only those rows)</p>
  
  <p>I am looking to update that table with some sort of CASE statement (I think) to force "10" entry to be equal to the SUM of the "20" entries.</p>
  
  <p>Table looks like this:</p>
  
  <pre><code>ITEM_ID    ENTRYTYPE    QTY       LOCATION_ID
  ------------------------------------------------
    A01        10          0       ALL-AVAILABLE   &lt;--THIS ROW SHOULD SHOW QTY=3
    A01        20          1          CABINET
    A01        20          2          FRIDGE
    A01        20          0          PANTRY
    A02        10          0       ALL-AVAILABLE   &lt;--THIS ROW SHOULD SHOW QTY=6
    A02        20          2          CABINET
    A02        20          2          FRIDGE
    A02        20          2          PANTRY  
  </code></pre>
  
  <p>I think the code is basically something like:</p>
  
  <pre><code>UPDATE Inventory 
  SET qty = SUM(t2.qty)
  FROM Inventory t2
  WHERE (ENTRYTYPE = 10 AND qty = 0) 
    AND ITEM_ID IN (list already generated to be pasted later);
  </code></pre>
  
  <p>Of course, I couldn't make this work for whatever reason. All help appreciated!</p>
  
  <p>TIA</p>