Oops, I think I misread what you want - it's actually simpler:

declare @count int
set @count = 0
update T
set @count = Seq_id = @count + 1
go

Steve

Ben Brugman wrote:

> This is partly a repeat.
>
> We had (it is solved now) a problem. During solving it I tried some
> constructions which did not work. In the end the problem was solved
> using a Delphi program with an ADO interface. This took about 2
> hours to run which was acceptable in our situation.
>
> My problem (partly psycological) ;
> I wasn't able to solve the problem within the given time frame using
> only tools supplied with SQL-server !
>
> The problem.
> An imported table with 3.6 millions rows, about 20 fields, 6 indexes
> and quite some relations with other tables.
> Three important fields where
> Object_id OID
> Seq_id S_ID
> date D
>
> OID and D together form a unique key. S_ID should be in the order
> of the date. But during transport and import this order was not
> kept correctly. So the S_ID had to be 'renumbered', with a specific
> range.
>
> The end solution was the Delphi program running over all OID, doing a
> SELECT on each OID with an ORDER on date and UPDATE the S_ID with a
> number then increase the number with one and continue.
> (The Delphi program was an adaptation from an existing program and
> took only minutes to build).
>
> My Question is :
> How to solve this kind of problem with only SQL-server tools ?
> Especially on larger tables. (millions or tens of millions of
> records).
>
> I tried to cluster the table in the correct order (OID,D), and
> make the S_ID an identity key starting with a correct seed
> for the range. (Tried this on a small table.) Problems it
> took too long, and the S_ID didn't get changed. From experience
> I know that the identity key does not always get created in
> the order of which the table is clustered. As said because
> of the time pressure with did the Delphi solution.
>
> Thanks in advance
>
> Ben Brugman

Report this post to a moderator