SQL Server : update every 5 records with serial number

Question

I'd like to add serial number based on every 5 records. There are some columns in my table: flag, seq_no and page_no.

If seq_no is equal to 1,2,3,4,5, page_no would be updated and set to 1.

If seq_no is equal to 6,7,8,9,10, page_no would be updated and set to 2, and so on.

I'm using SQL Server 2000.

My code as below but wrong:

    --set page_no
Declare @page_number int
Declare @i int, @k int

set @i = 1
set @k = 1

while @k <= 5
begin
     update dbo.test
     set @page_number = page_no = case 
                                    when @k = 5 then  (@i + 1)
                                    else @i
                                  end
     where flag = 'right'

     set @k = @k + 1
end

How can I do so? Please help me and so much thanks.

Answer 1

You could try something like:

UPDATE dbo.test
SET page_no = ((seq_no - 1) / 5) + 1

This will set the value of page_no to the value you want based on the value of seq_no.