Updating an INT column with a sequential value?

External Since: Dec 07, 2004 Posts: 1

I'm trying to assign a destinct value to 4,742 rows. How would I
construct a script to accomplish this starting with a value of 1000?
Thank you.

External Since: Dec 07, 2004 Posts: 31

Hi There,

Here's a trick ... given any table with primary key PK, one can
generate a sequential number using the following:

Consider: Table t1 (int1 int, char1 char(01), int2 int)
Primary Key on int1

insert t1 select 10,"A",0
insert t1 select 11,"B",0
insert t1 select 12,"C",0

select a.int1, c1=1000+count(*)
into t2
from t1 a, t1 b
where a.int1 >= b.int1
group by a.int1

update t1 set a.int2 = b.c1
from t1 a, t2 b
where a.int1 = b.int1

select * from t1

int1 char1 int2
----------- ----- -----------
10 A 1001
11 B 1002
12 C 1003

(3 rows affected)

I think this is what you want to do outside of using an identity column
of course ...

I'm not sure how this scales to very large tables ... good luck!!

Cheers,

Sara ...

woooopa wrote:
> I'm trying to assign a destinct value to 4,742 rows. How would I
> construct a script to accomplish this starting with a value of 1000?
> Thank you.