CODEWITHSUNDEEP
sqlsql-serversql-server-2008
RedLeffer
RedLeffer

Reputation: 87

SQL-Server 2008: How to update fields with a counter?

On PostgrSQL its no problem for me to do this:

CREATE SEQUENCE serial_olw START 1;
update collections_elements set elementorder=(nextval('serial_olw')-1) WHERE collections_elements.collectionid=1;
drop sequence serial_olw;

Example: 1,2,3,4,5,6...

On a MS-SQL Server 2008 there is not a function SEQUENCE... so I tried this:

DECLARE @i int
SET @i = 0

WHILE @i<44
    BEGIN
    UPDATE collections_elements set elementorder=(@i) WHERE collections_elements.collectionid=1
        SET @i=@i+1
    END

But I have no success with that loop...

Example: 43,43,43,43,43...

Any ideas for a solution?

Upvotes: 2

Views: 4537

Answers (4)

reza rostami
reza rostami

Reputation: 93

this script is a sample. Try something like this - inline variable increment:

DECLARE @i int 
SET @i = 1 

BEGIN
    UPDATE Questionnaire 
    SET prioritize = @i 
    ,  @i = @i +1 
    WHERE documentTypeCode=2 and groupCode=02
END

Upvotes: 0

Robert Lujo
Robert Lujo

Reputation: 16361

Try something like this - inline variable increment:

DECLARE @i int 
SET @i = 0

WHILE @i<44 
BEGIN
    UPDATE collections_elements 
    SET @i = elementorder = @i + 1 
    WHERE collections_elements.collectionid=1 
END

Upvotes: 0

Nilish
Nilish

Reputation: 1076

Update t
Set t.elementorder = t.RowID
From
(
    Select ROW_NUMBER() Over(Order by collectionid) as RowID, * From collections_elements
)t

SQL Fiddle

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

update T
set elementorder = rn
from
  (
    select elementorder,
           row_number() over(order by (select 0)) as rn
    from collections_elements
    where collectionid = 1
  ) T

SE-Data

Upvotes: 1

Related Questions