Update specific records of table based on subquery's output (sql server)

Question

have this query which produce below output :

select sec.site,sec.Name,sec.Freq from
( select Name from sec group by name having count(Freq) > 3)as dt
inner join sec on dt.Name = sec.Name
where sec.Freq like '%MUF2900%' ;


site    Name            Freq
S003   sct003a       MUF2900_26
S003   sct003a       MUF2900_26 
S003   sct003a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26

Now i have to update my another table name Test based on the output of above query. Table Test currently contains following records (before Update) :

 site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   1019581
 S003      29M00012   1019582
 S003      29M00013   1019583
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

 now for site S003 , ID for for cell starting from 09 and 29 are same ,so i need to update the ID for 29  i.e the unit place of ID should be incremented by 10 , like following..

REQUIRED :

  Site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   10195811
 S003      29M00012   10195812
 S003      29M00013   10195813
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

I have many records like this.

Note : S034 has no cell starting from 29 , so its ID dont need to update.

what i tried and where im getting confuse is :

 update Test set ID = (how to do this ??)
  where Test.site = (select sec.site,sec.Name,sec.Freq from
( select Name from sec group by Name having count(Freq) > 3)as dt
inner join sec on dt.Name = Sec.Name
where sec.Freq like '%MUF2900%') where cell like '29M%';

How should i do this. Do i need to make cursor for this ??

Answer 1

From your examples, the statement below should do what you want to do. It's not fast as it involves casting ID to a string and manipulating it, but I think this is the only thing that will work for you.

UPDATE TEST 
SET ID = CAST(LEFT(CAST(ID AS VARCHAR(20)), LEN(CAST(ID AS VARCHAR(20)) - 1)) + '1' + RIGHT(CAST(ID AS VARCHAR(20)), 1) AS INT)

If ID is a string to begin with then you can remove the CAST(ID AS VARCHAR(20)) statement and replace it with just ID.

UPDATE TEST 
SET ID = CAST(LEFT(ID, LEN(ID) - 1)) + '1' + RIGHT(ID, 1) AS INT)

Answer 2

ok i have solved it myself :

 UPDATE TEST 
 SET ID = CAST(SUBSTRING(CAST(ID AS NVARCHAR(20)), 1,LEN(CAST(ID AS VARCHAR(20))) - 1) + '1'  + RIGHT(CAST(ID as nvarchar(20)),1)as int)