title: T-SQL - Incremental update using subquery
body: <p>I'm trying to write a incremental update statement using SQL Server 2012. </p>
  
  <p>Current Data:</p>
  
  <pre><code>RecNo    Budget_ID  Item_Code  Revision
  ---------------------------------------
  1        16         xxx        2
  2        16         xxx        NULL
  3        16         xxx        NULL 
  12       19         yyy        3
  13       19         yyy        NULL
  14       19         yyy        NULL 
  15       19         yyy        NULL 
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>RecNo    Budget_ID  Item_Code  Revision
  ---------------------------------------
  1        16         xxx        2
  2        16         xxx        1
  3        16         xxx        0
  12       19         yyy        3
  13       19         yyy        2
  14       19         yyy        1
  15       19         yyy        0
  </code></pre>
  
  <p>However with following approach, I ended up with the result set as below.</p>
  
  <pre><code>UPDATE a
  SET a.Revision = (SELECT MIN(b.Revision) 
                    FROM [dbo].[foo] b 
                    WHERE b.item_code = a.item_code 
                      AND b.budget_id = a.budget_id
                    GROUP BY b.item_code ) -1
  FROM [dbo].[foo] a
  WHERE a.Revision is NULL
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>RecNo    Budget_ID  Item_Code  Revision
  ---------------------------------------
  1        16         xxx        2
  2        16         xxx        1
  3        16         xxx        1
  12       19         yyy        3
  13       19         yyy        2
  14       19         yyy        2
  15       19         yyy        2
  </code></pre>
  
  <p>Can anyone help me to get this right?</p>
  
  <p>Thanks in advance!</p>