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

I am very interested if that would be possible to use recursive CTE for that. If that is possible could someone provide an example pls. — Dimt
– Dimt, Commented Sep 11, 2014 at 8:17
I've already tired the recursion however, can't get result with distinct names. — Dimt
– Dimt, Commented Sep 11, 2014 at 8:20

Mikhail Timofeev · Accepted Answer · 2014-09-11 08:57:10Z

4

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

edited Sep 11, 2014 at 8:57

answered Sep 11, 2014 at 8:10

Mikhail Timofeev

2,16916 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CIPHER Over a year ago

I know the way which u said but I want to learn through update query in loop condition only............ and i tried following query and get row numbers but i didnt know how to assign in where condition...... code select ROW_NUMBER() over(order by (select 0)) from flatfile

CIPHER Over a year ago

U gave half solution only.....Incase if i my ID column has data in some fields and some fields having null means how your solution satisfies that criteria.... i just post an example in my question with all NULLs... i looking answer to update ID field using rownumber() or else any other way

Serpiton · Accepted Answer · 2014-09-11 08:57:30Z

3

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

answered Sep 11, 2014 at 8:57

Serpiton

3,6843 gold badges26 silver badges35 bronze badges

Comments

mj527 · Accepted Answer · 2014-09-11 08:08:07Z

0

Place the content in a cursor. In a loop, retrieve the cursor value and use it in the where condition of the update statement. You would have a unique id on the assumption that the combination of the name, department and fee_paid is unique.

answered Sep 11, 2014 at 8:08

mj527

1613 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to update particular column fields with while loop

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related