Update column with range of sequense numbers

Transact-SQL
1

I have one column in a table that I need to do an update on. In the column the value is a string 'MHB'. What I need the update to do is update all the 'MHB' to 'MHB1' , 'MHB2', 'MHB3' and 'MHB4' then I need it to start over and repeat 'MHB1', 'MHB2', 'MHB3' and 'MHB4'
So if the column has these values:
MHB
MHB
MHB
MHB
MHB
MHB
After the update the column should look like this:
MHB1
MHB2
MHB3
MHB4
MHB1
MHB2

Is there anyway of doing this in one update statement?
If not what is the quickest way of doing this. And when I mean quickest I mean for the whole update to take to complete. Like if we have 500,000 records I would want the shortest time to complete doing all 500,000 records.
Thank you for your help

2

Assuming you have an unique/primary key on your table (in my example called id), you might use this:

create table #yourtable (id int,col varchar(10));

insert into #yourtable (id,col)
 values(1,'MHB')
      ,(2,'MHB')
      ,(3,'CHB')
      ,(4,'CHB')
      ,(5,'MHB')
      ,(6,'CHB')
      ,(7,'MHB')
      ,(8,'MHB')
      ,(9,'MHB')
;

update a
   set a.col=a.col+b.rn
  from #yourtable as a
       inner join (select id
                         ,ltrim(str((row_number() over(order by id)-1)%4+1)) as rn
                     from #yourtable
                    where col='MHB'
                  ) as b
               on b.id=a.id
 where col='MHB'
;

select *
  from #yourtable
;

drop table #yourtable;
1 Like