--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
create table [table1]([id] varchar(40),[name] int)
insert [table1]
select 'aa',1 union all
select 'b',2 union all
select 'ccc',3 union all
select 'dd',4 union all
select 'eeee',5
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 
create table [table2]([id] int,[name] varchar(70))
insert [table2]
select 1,'aacdf' union all
select 2,'eeeesdf' union all
select 3,'ddgret' union all
select 4,'bgfder' union all
select 5,'ccc234'


update 
  b
set
  name=stuff(b.name,1,len(a.id),ltrim(a.name))
from
  table1 a join table2 b
on
  b.name like a.id+'%'
  
  
select * from table2
/*


id          name                                                                   
----------- ---------------------------------- 
1           1cdf
2           5sdf
3           4gret
4           2gfder
5           3234

（所影响的行数为 5 行）

------解决方案--------------------

SQL code
这样好了

update 
  b
set
  name=ltrim(a.name)+replace(b.name,a.id,'')
from
  table1 a , table2 b
where
  charindex(a.id,b.name)>0