SQL2008，表bmdh,表QQMA，有相同的列:姓名，电话，当两表电话相同而姓名不同时，且BMDH表中的姓名列中最后一个逗号后的字符＝QQMA.姓名,则在解决办法

create table BMDH (name nvarchar(1200),phone varchar(120))gocreate table QQMA (name nvarchar(1200),phone varchar(120))goinsert BMDH  select '张三,赵一脚D4.鬼人,张三','5566742' union select '小九C3.龙,李四','13088889999'union select '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666'union select '小九C3.龙, ZZZZ.天,鬼子六','3322152' unionselect '风风.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 BMDHdrop 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 allselect '小九C3.龙,李四','13088889999'  union allselect '红河道.换采,风风.EE,我是风儿.狼月,赵五','13877776666'  union allselect '小九C3.龙,ZZZZ.天,鬼子六','3322152' union allselect '风风.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 allselect '李四','13088889999' union allselect '赵五','13877776666' union allselect '鬼子六','3322152'update [BMDH]set [姓名]=case when RIGHT([BMDH].[姓名],charindex(',',REVERSE([BMDH].[姓名]))-1)=a.[姓名]then LEFT([BMDH].[姓名],LEN([BMDH].[姓名])-charindex(',',REVERSE([BMDH].[姓名]))) else [BMDH].[姓名] endfrom [QQMA] a where [BMDH].电话=a.电话select * from [BMDH]/*姓名    电话张三,赵一脚D4.鬼人    5566742小九C3.龙    13088889999红河道.换采,风风.EE,我是风儿.狼月    13877776666小九C3.龙,ZZZZ.天    3322152风风.EE,我是风儿.狼月,赵五    15987489999*/