如何根据一个张替换两一张表某个字段的部分值

怎么根据一个张替换两一张表某个字段的部分值
table1  

  id name  
  aa 1  
  b 2  
  ccc 3  
  dd 4  
  eeee 5  
  .. ..  

table2

id name
1 aacdf
2 eeeesdf
3 ddgret
4 bgfder
5 ccc234
  .. ..  

需要根据table1，把table2中name字段里开头和table1 id字段相符的替换成table1.id对应的name,注意table1的id字段只替换table2的name字段的开头，结果如下

table2

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

[解决办法]

SQL code

--> 测试数据：[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 allselect 'b',2 union allselect 'ccc',3 union allselect 'dd',4 union allselect '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 allselect 2,'eeeesdf' union allselect 3,'ddgret' union allselect 4,'bgfder' union allselect 5,'ccc234'update   bset  name=stuff(b.name,1,len(a.id),ltrim(a.name))from  table1 a join table2 bon  b.name like a.id+'%'    select * from table2/*id          name                                                                   ----------- ---------------------------------- 1           1cdf2           5sdf3           4gret4           2gfder5           3234（所影响的行数为 5 行）
[解决办法]
SQL code
这样好了update   bset  name=ltrim(a.name)+replace(b.name,a.id,'')from  table1 a , table2 bwhere  charindex(a.id,b.name)>0