title: Update more than one row getting max value
body: <p>I have one table.</p>
  
  <pre><code>ID       SID     CID      Number
  ----------------------------------
  1237      1       5        NULL
  1236      1       5        NULL
  1235      1       NULL      2
  1234      1       NULL      2
  1233      1       NULL      1
  1232      1       NULL      1
  </code></pre>
  
  <p>I want to get max Number value. And Update Number column which is NULL </p>
  
  <pre><code>DECLARE @Number INT;
  SELECT @Number =  max([Number]) FROM STACKOVERFLOW WHERE [Number] IS NOT NULL AND SID = 1
  SELECT @Number 
  </code></pre>
  
  <p>This SQL statement will return 2.</p>
  
  <p>As I said, I want to update number column which is <code>null</code> but it should be <code>"maximum value of number column"</code> + 1, that is to say in my case it should be 3.</p>
  
  <pre><code>DECLARE SID INT = 1;
  
  UPDATE STACKOVERFLOW 
  SET
  Number = (
            SELECT TOP 1 CASE 
            WHEN Number IS NULL THEN 1 ELSE @Number + 1 END 
            FROM STACKOVERFLOW 
            WHERE SID = @SID AND Number IS NULL
           )
  FROM STACKOVERFLOW WHERE ID = 1237 
  </code></pre>
  
  <p>The table will be like below:</p>
  
  <pre><code>ID       SID     CID      Number
  ----------------------------------
  1237      1       5         3
  1236      1       5        NULL
  1235      1       NULL      2
  1234      1       NULL      2
  1233      1       NULL      1
  1232      1       NULL      1
  </code></pre>
  
  <p>After that, when again sql statement works for <code>ID = 1236</code>  and at that time @Number will return 3 and 
  the record will be like below.</p>
  
  <pre><code>ID       SID     CID      Number
  ----------------------------------
  .
  1236      1       5        4
  .
  .
  </code></pre>
  
  <p>But the result that I want is </p>
  
  <pre><code>ID       SID     CID      Number
  ----------------------------------
  .
  1236      1       5      3
  .
  </code></pre>
  
  <p>At the end of operations the table should be like</p>
  
  <pre><code>ID       SID     CID      Number
  ----------------------------------
  1237      1       5         3
  1236      1       5         3
  1235      1       NULL      2
  1234      1       NULL      2
  1233      1       NULL      1
  1232      1       NULL      1
  1231      2       5         5
  1230      2       5         5
  1229      2       NULL      4
  1228      2       NULL      4
  </code></pre>
  
  <p>Any idea? 
  Thanks for your replies.</p>