Update a column of a table to be a sequence

Question

I want to update a table's column using the results of a stored procedure

create procedure seq
as
DECLARE @NextValue INT;
INSERT SequencesTable DEFAULT VALUES;
DELETE SequencesTable WITH(READPAST); 
SELECT SCOPE_IDENTITY();
go

I can't use a UDF since it is nodeterministic. Something like this won't work

UPDATE [dbo].[t1]     SET [c1] = seq

I have a feeling I am approaching this the wrong way.

I just want to do update a table that looks like this

1 1
2 2
1 4
1 4
5 5
1 2

To look like this

1 1
2 2
3 4
4 4
5 5
6 2

Answer 1

Try something like this:

update a set c1 = c1_new
from (
  select 
    c1, c2
  , c1_new = row_number() over (order by nullif(c1,c1))
  from dbo.t1
  ) a

Play around with the inner query until you get something you like, then apply the update.

The NULLIF(c1,c1) attempts to preserve original order on disk. It always returns null.

Answer 2

This script really helped me. Thanks!!!

Regards, Carlos Mejia Granada, Nicaragua