How to update particular column fields with while loop

Question

SQL Server 2008

My SQL table is Like below

----------------------------------------
**name  department  fee_paid    id**
----------------------------------------
Farooq  ECE          10000     NULL
Khan    EEE          20000     NULL
Syed    Chemistry    4000      NULL
Syed    Chemistry    14000     NULL
Yousuf  Physics      2000      NULL
Yousuf  Physics      18000     NULL
Zubair  EEE          4000      NULL
----------------------------------------

now i wish to fill data in id fields like below

----------------------------------------
**name  department  fee_paid    id**
----------------------------------------
Farooq  ECE          10000     1000
Khan    EEE          20000     1001
Syed    Chemistry    4000      1002
Syed    Chemistry    14000     1003
Yousuf  Physics      2000      1004
Yousuf  Physics      18000     1005
Zubair  EEE          4000      1006
----------------------------------------

i tried like below but it stores same value in all id fields..I know as i miss my where condition in update query below.But how i use where condition with above table criteria because it has duplicates ?

declare @i as int =1000
while @i<=1006
begin
    update flatfile set id=@i
    set @i+=1
end

Answer 1

Why don't you use ALTER TABLE?

alter table flatfile 
add ID int identity(1000,1) 

EDIT - if you want to do it in loop:

declare @i as int = 1000
while @i<=1006
begin
    update top(1) flatfile set id=@i 
    where id is null;
    set @i+=1
end

Answer 2

To get the ID it's possible to use the Row_Number function.

Windowing functions are not allowed in an update script, but we can write an updatable view, or a CTE, and work with it instead:

WITH CTE AS (
  SELECT [name], [department], [fee_paid], [id]
       , num = Row_Number()
         OVER (ORDER BY (SELECT NULL)) + 999
  FROM   table1
)
UPDATE CTE SET
  ID = num;

SQLFiddle Demo