Update records in groups of 5 with WHILE

[bmacbmac]

Hello. I have a few hundred thousand records in sql 2005. I have the following data:

create table #temp2 (tempid int identity(1,1), irelatedid int, irecordid int, newirecordid int)
insert into #temp2 values (1500, 2000, NULL)
insert into #temp2 values (1501, 2000, NULL)
insert into #temp2 values (1502, 2000, NULL)
insert into #temp2 values (1503, 2000, NULL)
insert into #temp2 values (1504, 2000, NULL)
insert into #temp2 values (1505, 2000, NULL)
insert into #temp2 values (1506, 2000, NULL)
insert into #temp2 values (1507, 2000, NULL)
insert into #temp2 values (1508, 2000, NULL)
insert into #temp2 values (1509, 2001, NULL)
insert into #temp2 values (1510, 2001, NULL)
insert into #temp2 values (1512, 2001, NULL)
insert into #temp2 values (1513, 2001, NULL)
insert into #temp2 values (1514, 2001, NULL)
insert into #temp2 values (1505, 2002, NULL)

I would like to set the NewIrecordid to an @NewIrecordid variable + 1 in groups of 5 irecordid's at a time.

I'm sure I'm making this way too complicated, but If I run something like this:

declare @irecordid int
declare @count int
declare @newirecordid int


set @irecordid = (select min(irecordid) from #temp2 where newirecordid is null)
set @count = 0
set @newirecordid = 200001

while (@count <5)
begin
update #temp2
set newirecordid = @newirecordid
where irelatedid in (select min(irelatedid) from #temp2 where irecordid = @irecordid
and newirecordid is null)
set @count = @count+1
end

[code]

It updates my NewIrecordid to 200001 just fine, but I would like it to loop again and set the next group of 5 to @newrecordid + 1 (200002).

So I guess my question is, how can I get this loop to continue running in a larger loop that resets my @newirecordid with each sequence of 5?

Thanks!

Brian

 

[markros]

Most likely you don't need any loop here and can use ROW_NUMBER function.

PluralSight Learning Library

 

[bmacbmac]

I tried that but just couldn't get it to work. How can I use that function to do something every 5 rows?

 

[markros]

select *, row_number() over (partition by (id-1)/5 order by id) as Rn5InGroup ....

Assuming that Id starts with 1 and doesn't have gaps.

PluralSight Learning Library