Update column but leave one instance the same

1

I have this SP that goes into the OPS_USAGE_TBL and updates all the SOURCE_IDs from '401-MHB' to 401-MHB1,401-MHB2, all the way up to 401-MHB6 then on the next one it goes back to 401-MHB1 and continues on until no more 401-MHBs in the column. It works great but I was hoping that I could leave 401-MHB in the sequence. So instead of having
401-MHB1
401-MHB2
401-MHB3
401-MHB4
401-MHB5
401-MHB6
401-MHB1
401-MHB2 etc...
For the results I would have
401-MHB
401-MHB1
401-MHB2
401-MHB3
401-MHB4
401-MHB5
401-MHB6
401-MHB
401-MHB1
401-MHB2 etc...

Don't want to use the cursor method. Is there a way to do this with an update?

Here's what I have now:

update a
set a.SOURCE_ID =Rtrim(a.SOURCE_ID)+b.rn
from OPS_USAGE_TBL as a
inner join (select serial
,ltrim(str((row_number() over(order by serial)-1)%6+1)) as rn
from OPS_USAGE_TBL
where SOURCE_ID='401-MHB'
) as b
on b.serial=a.serial
where SOURCE_ID='401-MHB'

Thanks

2

update a
set a.SOURCE_ID =Rtrim(a.SOURCE_ID)+case when b.rn = '0' then '' else b.rn end
from OPS_USAGE_TBL as a
inner join (select serial
,ltrim(str((row_number() over(order by serial)-1)%7)) as rn
from OPS_USAGE_TBL
where SOURCE_ID='401-MHB'
) as b
on b.serial=a.serial
where SOURCE_ID='401-MHB'

N.B. NOT TESTED !!! Note that there are two changed lines.