SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table df_umts_relation(cell_name int
                              , n_cell_name  int
                              , technology varchar(20)
                              , target_ops_num int)
insert into df_umts_relation values (121, 221, 'UMTS', null)
1 rows affected

insert into df_umts_relation values (122, 222, 'GSM', null)
1 rows affected

create table df_umts_carrier (opsnum_umts int, cell_name_umts varchar(20))
insert into  df_umts_carrier values(221, '12345')
1 rows affected

create table df_gsm_carrier (opsnum_umts int, cellname int)
insert into df_gsm_carrier values(222, '6789')
1 rows affected

select * from df_umts_relation;


cell_name
n_cell_name
technology
target_ops_num


121
221
UMTS
null

122
222
GSM
null


update df_umts_relation
set target_ops_num = ( select case when dur.technology = 'UMTS' then du.cell_name_umts
                                   when dur.technology = 'GSM' then dg.cellname
                              end
from df_umts_relation dur
left join df_umts_carrier du on  dur.n_cell_name = du.opsnum_umts
left join df_gsm_carrier dg on dur.n_cell_name = dg.opsnum_umts
where dur.n_cell_name = df_umts_relation.n_cell_name)
2 rows affected

select * from df_umts_relation;


cell_name
n_cell_name
technology
target_ops_num


121
221
UMTS
12345

122
222
GSM
6789


select case when dur.technology = 'UMTS' then du.cell_name_umts
                                   when dur.technology = 'GSM' then dg.cellname
                              end
from df_umts_relation dur
left join df_umts_carrier du on  dur.n_cell_name = du.opsnum_umts
left join df_gsm_carrier dg on dur.n_cell_name = dg.opsnum_umts


(No column name)


12345

6789