怎么把数据库的某一字段的值更新，谢谢
怎么把数据库的某一字段的值更新
比如“6”更新成“3”
比如"5,6,9,5"更新成"2,3,6,2"
就是第一个用逗头分开的值都要减3
[解决办法]
这样:
--drop table t2
create table t2(n varchar(20))
insert into t2
values('5,6,9,5')
select cast(cast(left(n,charindex(',',n)-1) as int) - 2 as varchar) +
stuff(n,1,charindex(',',n)-1,'')
from t2
/*
3,6,9,5
*/
create table hn
(x varchar(20))
insert into hn
select '6' union all
select '5,6,9,5'
-- 更新
with t as
(select a.x,
substring(a.x,b.number,charindex(',',a.x+',',b.number)-b.number)-3 'y'
from hn a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.x)
and substring(','+a.x,b.number,1)=',')
update a
set a.x=b.h
from hn a
inner join
(select c.x,
stuff((select ','+rtrim(d.y) from t d
where d.x=c.x
for xml path('')),1,1,'') 'h'
from t c
group by c.x
) b on a.x=b.x
-- 结果
select * from hn
/*
x
--------------------
3
2,3,6,2
(2 row(s) affected)
*/
DECLARE @XmlDocumentHandle int
declare @XmlDocument nvarchar(4000)
set @XmlDocument='5,6,9,5'
set @XmlDocument=REPLACE(@XmlDocument,',','</item><item>')
set @XmlDocument=N'<Root><item>'+@XmlDocument+'</item></Root>'
select o.value('.','int') 'item'
into #temp
from (select cast(@XmlDocument as xml) 'x') t
cross apply x.nodes('/Root/item') x(o)
declare @s varchar(1000)
set @s=''
select @s=@s+','+cast(item-3 as varchar) from #temp
select STUFF(@s,1,1,'')
drop table #temp
/*
2,3,6,2
*/
