实在没辙了.SQL更新字段的值.
字段: [Counts]
旧值: C:5,E:1;C:6,E:2
新值: C:5,D:5,E:1;C:6,D:6,E:2
下面看起来更明白一些
旧值: C:5,****E:1;C:6,****E:2
新值: C:5,D:5,E:1;C:6,D:6,E:2
插入的D的值要跟C的值一样
我只想到了替换,总是不成功.
大家帮帮我!
[解决办法]
规则不明确呀？ 里面是C E ，进来的D放在C和E的中间。
如果里面是 C N P 是不是新进来的也要按字母顺序排序呀？
[解决办法]
替换是比较好的方法，比如用C：5替换成C：5，D：5。
但是需要一个while循环替换所有符合的字符串
看楼主的数据库设计，建议还是尽早重新弄一下比较好。现在已经是噩梦了。
[解决办法]
没看明白，最好举个例子
[解决办法]
你最好给出几条你真实的数据，然后附上你期待的更新结果
[解决办法]
什么人设计的数据库啊，第一范式都没达到
[解决办法]
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[name] VARCHAR(100))INSERT [tb]SELECT 1,'C:5,E:1;C:6,E:20' UNION ALLSELECT 2,'C:5,E:1,A:21;C:55,E:7,A:21' UNION ALLSELECT 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 pGOcreate proc p@old_name varchar(100),@new_name varchar(100)asset nocount onbeginIF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1create table #t1(iden int identity ,id int,name varchar(10),value varchar(10))IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2create 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 #t3create 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 #t3where 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)aouter apply ( select [name] = N.v.[value]('.', 'varchar(10)') from a.[name].nodes('/ROOT/V') N(v))b--拆分分号后面的值insert into #t2select 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)aouter 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_valuefrom #t1 a left join #t2 bon 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 cte3update a set a.name=left(a.name,charindex(';',a.name))+b.namefrom tb a,cte3 b where a.id=b.iddrop table #t1,#t2,#t3end--测试select * from tbexec p 'C:5,E:1','C:5,D:7,E:1'select * from tbexec 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,D:7,F:12,G:15,E:1;C:6,D:6,F:6,G:6,E:202 C:5,D:7,F:12,G:15,E:1,A:21;C:55,D:55,F:55,G:55,E:7,A:213 C:5,D:7,F:12,G:15,E:1;C:10,D:10,F:10,G:10,E:20(3 行受影响)*/