create table BMDH (name nvarchar(1200),phone varchar(120))
go
create table QQMA (name nvarchar(1200),phone varchar(120))
go

insert BMDH  
select '张三,赵一脚D4.鬼人,张三','5566742' union 
select '小九C3.龙,李四','13088889999'union 
select '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666'union 
select '小九C3.龙, ZZZZ.天,鬼子六','3322152' union
select '风风.EE,我是风儿.狼月,赵五','15987489999'


insert QQMA 
select '张三','5566742'union 
select '李四','13088889999'union 
select '赵五','13877776666'union 
select '鬼子六','3322152'
go

 select * from BMDH  order by name desc
 /*
 name                                        phone
张三,赵一脚D4.鬼人,张三                        5566742
小九C3.龙,李四                                13088889999
小九C3.龙, ZZZZ.天,鬼子六                    3322152
红河道.换采,风风.EE,我是风儿.狼月,赵五        13877776666
风风.EE,我是风儿.狼月,赵五                    15987489999
 */


update BMDH   set name =SUBSTRING(BMDH.name,1,len(BMDH.name)-len(substring(REVERSE(BMDH.name),1,CHARINDEX(',',REVERSE(BMDH.name))))) 
from QQMA  where BMDH.phone=QQMA.phone and reverse(substring(REVERSE(BMDH.name),1,CHARINDEX(',',REVERSE(BMDH.name))-1))=QQMA.name
 
 select * from BMDH  order by name desc
 /*
 name                                        phone
张三,赵一脚D4.鬼人                            5566742
小九C3.龙, ZZZZ.天                            3322152
小九C3.龙                                    13088889999
红河道.换采,风风.EE,我是风儿.狼月            13877776666
风风.EE,我是风儿.狼月,赵五                    15987489999
*/

drop table BMDH
drop table QQMA

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

SQL code

--> 测试数据：[BMDH]
if object_id('[BMDH]') is not null drop table [BMDH]
create table [BMDH]([姓名] varchar(100),[电话] varchar(14))
insert [BMDH]
select '张三,赵一脚D4.鬼人,张三','5566742'  union all
select '小九C3.龙,李四','13088889999'  union all
select '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666'  union all
select '小九C3.龙,ZZZZ.天,鬼子六','3322152' union all
select '风风.EE,我是风儿.狼月,赵五','15987489999' 
--> 测试数据：[QQMA]
if object_id('[QQMA]') is not null drop table [QQMA]
create table [QQMA]([姓名] varchar(6),[电话] varchar(14))
insert [QQMA]
select '张三','5566742' union all
select '李四','13088889999' union all
select '赵五','13877776666' union all
select '鬼子六','3322152'

update [BMDH]
set [姓名]=case when RIGHT([BMDH].[姓名],charindex(',',REVERSE([BMDH].[姓名]))-1)=a.[姓名]
then LEFT([BMDH].[姓名],LEN([BMDH].[姓名])
-charindex(',',REVERSE([BMDH].[姓名]))) else [BMDH].[姓名] end
from [QQMA] a where [BMDH].电话=a.电话

select * from [BMDH]

/*
姓名    电话
张三,赵一脚D4.鬼人    5566742
小九C3.龙    13088889999
红河道.换采,风风.EE,我是风儿.狼月    13877776666
小九C3.龙,ZZZZ.天    3322152
风风.EE,我是风儿.狼月,赵五    15987489999
*/