Update a field with a counter incrementing after group by

[helpmonny]

I'm trying to write a stored procedure to update a field for all rows in a table. I need to pass in a starting value and update all rows for group1 and then increment it by one and update all rows for group2 and so on (the 'groupfield' values won't be in order either). Also, if the groupcount of a group goes over 30 I need to increment by 1 also and continue on.

I have records:
id, groupfield, updatefield
1,1,null
2,7,null
3,1,null
4,5,null
5,2,null
if I start passing '6' for the first value of updatefield I should end up with:
1,1,6
2,7,9
3,1,6
4,5,8
5,2,7
Any help/ideas much appreciated.

 

[JBaileys]

Maybe - I don't understand your objective. But by passing a '6' for the updatefield. Should row 2 be 2,7,8 ?

Could you clarify the rules?

 

[SQLBill]

I follow the poster...just go by the second column. The OP wants it updated by the second column and even though they are out of order it's still:

1 (grp 1)
1 (grp 1)
2 (grp 2)
5
7

What I would like clarification on is, how should missing numbers be handled? 3,4,and 6 are missing. Should they be accounted for? Will they ever be added later on?

-SQLBill

Posting advice: FAQ481-4875

 

[helpmonny]

The missing numbers don't need to be accounted for. The groupfield column is just an id linking to another table so could be any number. The groupfield column could be anything really, it simply needs to be grouped and the updatefield is the important one which will be an integer incrementing by one for each group and incrementing again if the group >30 records.

 

[SQLBill]

Disclaimer: This is only a starting point and I have not tested it.

You might try setting this into a loop.

DECLARE @updatenbr INT
 SET @updatenbr = 6
<start loop here>
UPDATE tablename
  SET UpdateField = @updatenbr
  WHERE GroupField = (SELECT MIN(GroupField)
                        FROM tablename
                        WHERE UpdateField IS NULL)
 SET @updatenbr = @updatenbr + 1

-SQLBill

Posting advice: FAQ481-4875

 

[helpmonny]

Thanks SQLBill with a 'while' clause this works perfectly.

DECLARE @updatenbr INT
SET @updatenbr = 6
while exists (select * from mrrpay where code is null)
begin
UPDATE mrrpay
SET code = @updatenbr
WHERE ic = (SELECT MIN(ic)
FROM mrrpay
WHERE code IS NULL)
SET @updatenbr = @updatenbr + 1
end

Now I'll try to amend slightly to also increment after 30 records.
Thanks for the help.