Update column with sequence number every time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update column with sequence number every time

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-25 : 02:24:35

hello friends

i want to update one column from my table regularly on sequence number i.e. 0,1,2,3,4,5

i created procedure but it is not working as per my output

declare @IndexIDGen int
declare @ID int
set @ID = 0
update temp_test set indexid = NULL
declare IndexIDGen cursor for select indexid from temp_test
open IndexIDGen
FETCH Next from IndexIDGen into @IndexIDGen
while @@fetch_status = 0
begin
update temp_test set indexid = @ID where indexid is null

set @ID = @ID + 1
print @id
fetch next from IndexIDGen into @IndexIDGen
end

close IndexIDGen
deallocate IndexIDGen

where i am going in wrong direction ??????

T.I.A

Hariarul
Posting Yak Master

160 Posts

Posted - 2006-09-25 : 02:35:51

Try using a PK column from the table temp_test while updating in WHERE clause as well as in SELECT clause of the cursor

HHA

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 02:43:26

When you want to update that column, i.e. while inserting the records, then keep that column as Identity.

Is there any Primary key column in your database, if so then let us know that.

This can be acheive without using cursors.

Chirag

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-25 : 02:44:44

hi!!

i want this at run time i already created pk to other column..i want this SP at run time and arrange the records as per this sequence..

T.I.A

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 02:48:57

your cursor should be somthing like this


declare @IndexIDGen int
declare @ID int
set @ID = 0
update temp_test set indexid = NULL
declare IndexIDGen cursor for select Primarykey from temp_test Where indexid is null 
open IndexIDGen
FETCH Next from IndexIDGen into @IndexIDGen
while @@fetch_status = 0
begin
     update temp_test set indexid = @ID where Primarykey = @IndexIDGen
     set @ID = @ID + 1
     print @id
     fetch next from IndexIDGen into @IndexIDGen
end

close IndexIDGen
deallocate IndexIDGen

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 02:49:55

quote:
Originally posted by under2811

hi!!

i want this at run time i already created pk to other column..i want this SP at run time and arrange the records as per this sequence..

T.I.A

Let us know what is your primary key and what is its datatype?

Chirag

Hariarul
Posting Yak Master

160 Posts

Posted - 2006-09-25 : 02:58:03

Could you please try the below code - NOTE : I didnt test it ...

DECLARE @TabIdenGen Table ( PkCol INT , ID INT IDENTITY(0,1) Not Null)

INSERT INTO @TabIdenGen (PkCol)
SELECT PK FROM temp_test WHERE indexid IS NULL

UPDATE temp_test
SET indexid = T.ID
FROM temp_test A , @TabIdenGen T
WHERE A.PkCol = T.PKCol

HHA

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-25 : 03:22:10

hi all..

i got it...

thanks all :) :)

Hariarul
Posting Yak Master

160 Posts

Posted - 2006-09-25 : 04:51:06

Did the @TabIdenGen logic work ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources