--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(100))
INSERT [tb]
SELECT 1,'C:5,E:1;C:6,E:20' UNION ALL
SELECT 2,'C:5,E:1,A:21;C:55,E:7,A:21' UNION ALL
SELECT 3,'C:5,E:1;C:10,E:20'
GO


--> 
-- 假设你要更新的条件是分号前面的。
-- 如更新条件为 update [tb] set [name]='C:5,D:6,E:1'+right([name],len([name])-7) where left([name],7)='C:5,E:1' 
--如果不是这样就没必要往下面看了
-- 次方法适用于2005 2008
-- 可以把条件 name 改成 id
-- 支持插入人一个值。
IF OBJECT_ID('p') IS NOT NULL DROP proc p
GO
create proc p
@old_name varchar(100),
@new_name varchar(100)
as
set nocount on
begin

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
create table #t1(iden int identity ,id int,name varchar(10),value varchar(10))
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2
create table #t2(id int,name varchar(10),value varchar(10))
IF OBJECT_ID('f_str',N'FN') IS NOT NULL DROP function f_str
--存跟新前后的name值变化
IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3
create table #t3(id int,new_name varchar(100))

--第一次更新，取出更新后的值
update tb set [name]=@new_name+right([name],len([name])-len(@old_name)) 
output deleted.id,inserted.name into #t3
where left([name],len(@old_name))=@old_name
--select * from #t3
--拆分分号前面的值
insert into #t1 
select a.id, 
    name=left(b.name,charindex(':',b.name)-1),
    value=right(b.name,len(b.name)-charindex(':',b.name))
from(
    select id, 
    [name] = CONVERT(XML,'<ROOT><V>' + REPLACE(left(new_name,charindex(';',new_name)-1), ',', '</V><V>') + '</V></ROOT>') 
    from #t3
)a
outer apply (
    select [name] = N.v.[value]('.', 'varchar(10)') 
    from a.[name].nodes('/ROOT/V') N(v)
)b
--拆分分号后面的值
insert into #t2
select a.id, 
    name=left(b.name,charindex(':',b.name)-1),
    value=right(b.name,len(b.name)-charindex(':',b.name))
from(
    select id, 
    [name] = CONVERT(XML,'<ROOT><V>' + REPLACE(right(new_name,len(new_name)-charindex(';',new_name)), ',', '</V><V>') + '</V></ROOT>') 
    from #t3
)a
outer apply (
    select [name] = N.v.[value]('.', 'varchar(10)') 
    from a.[name].nodes('/ROOT/V') N(v)
)b

--进行查询合并重组
;with cte as
(
select a.iden,a.id,a.name as a_name,a.value as a_value,isnull(b.name,a.name) as b_name, 
b.value as b_value
from #t1 a left join #t2 b
on a.name=b.name and a.id=b.id
)
,cte2 as
(
select  id,a_name,a_value,b_name,b_value=isnull(b_value,(select top 1 b_value from cte where iden<t.iden and b_value is not null order by iden desc)) 
from cte t
)
--select * from cte2 
,cte3 as
(
select id,stuff((select ','+b_name+':'+b_value from cte2 where id=b.id for xml path('')),1,1,'') as name 
from cte2 b group by id
)
--select * from cte3
update a set a.name=left(a.name,charindex(';',a.name))+b.name
from tb a,cte3 b where a.id=b.id

drop table #t1,#t2,#t3

end


--测试
select * from tb
exec p 'C:5,E:1','C:5,D:7,E:1'
select * from tb
exec p 'C:5,D:7,E:1','C:5,D:7,F:12,G:15,E:1'
select * from tb
/*
id          name
----------- ----------------------------------------------------------------
1           C:5,