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