Update more than one row getting max value

Question

I have one table.

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

I want to get max Number value. And Update Number column which is NULL

DECLARE @Number INT;
SELECT @Number =  max([Number]) FROM STACKOVERFLOW WHERE [Number] IS NOT NULL AND SID = 1
SELECT @Number 

This SQL statement will return 2.

As I said, I want to update number column which is null but it should be "maximum value of number column" + 1, that is to say in my case it should be 3.

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 

The table will be like below:

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

After that, when again sql statement works for ID = 1236 and at that time @Number will return 3 and the record will be like below.

ID       SID     CID      Number
----------------------------------
.
1236      1       5        4
.
.

But the result that I want is

ID       SID     CID      Number
----------------------------------
.
1236      1       5      3
.

At the end of operations the table should be like

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

Any idea? Thanks for your replies.

Answer 1

UPDATE STACKOVERFLOW
   SET Number = (SELECT MAX(number) + 1 FROM STACKOVERFLOW WHERE SID = 1)
 WHERE Number IS NULL
   AND SID = 1

Then do the same thing for SID = 2

UPDATE STACKOVERFLOW
   SET Number = (SELECT MAX(number) + 1 FROM STACKOVERFLOW WHERE SID = 2)
 WHERE Number IS NULL
   AND SID = 2

Or to make it a bit more elegant:

UPDATE STACKOVERFLOW
   SET Number = (SELECT MAX(number) + 1 FROM STACKOVERFLOW WHERE SID = A.SID)
  FROM STACKOVERFLOW A
 WHERE Number IS NULL

Answer 2

Either set all at once or also check CID for NULL