title: How to Update Table with Using Inner Join and Case Statement
body: <p>Problem :</p>
  
  <p>Table1</p>
  
  <pre><code>CatId - -   Type - -    Qty
  ==============================
  8        ||    O   ||   10
  8        ||    N   ||   20
  8        ||    U   ||   30
  30       ||    N   ||   5
  30       ||    O   ||   15
  30       || NULL   ||   25
  </code></pre>
  
  <hr>
  
  <p>Table2</p>
  
  <pre><code>catId -- Old -    -New -- Useless -- Other
  ========================================
  8      || 100   || 70   ||  140      || 110
  30     || 10    || 20   ||  30       || 50
  </code></pre>
  
  <hr>
  
  <p>Result: Update Table 2  with Table 1 Like</p>
  
  <pre><code>-------------------------------------------------
  catId --   Old --   New -- Useless -- Other
  8       || 90    || 50  ||  110     || 110
  30      ||  5    ||  5  ||  30      || 25
  </code></pre>
  
  <p>How result should come : </p>
  
  <p>Table 1 and Table 2 has a common column CatId.</p>
  
  <pre><code>Column of table 1 Type is connects with Table2  
      AS  (Old - O / New - N / Useless - U / Other - NULL)
  </code></pre>
  
  <p>I want subtract Like table2(Respective  O/N/U/Other)  = table2(Respective O/N/U/Other) - table1(Type) and would prefer solution without loop</p>
  
  <p>I tried this but not working Properly --</p>
  
  <pre><code>Update Table2
  Set New = New - (CASE Type WHEN 'N' THEN (Table1.qty) Else 0 End),
      Old = Old  - (CASE Type WHEN 'O' THEN (Table1.qty) Else 0 End),
      Old = Old  - (CASE Type WHEN 'O' THEN (Table1.qty) Else 0 End),
      Other= Othere- (CASE Type WHEN is Null THEN (Table1.qty) Else 0 End)
  from table1 
  inner join table2 
  On table1.catId = table2 .catId
  </code></pre>