0

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 ??

0

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)
  • Sorry to say , but it is not working as it is converting unit place to hundred and thats only in case when unit place is 1, not in case when unit place is 2 and 3..moreover , i have many records like this , so it will not work – Ankit Kapoor Sep 17 '14 at 8:47
  • That wasn't in your original question. You could use a case statement to check for the value of the unit place and then increment by 10 if it's 1. – Jonathan Fite Sep 22 '14 at 22:15
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.