有表A
id path
----------------
s1 sadfxyzsdfi
s2 uipxyzkl
s3 hjlxyzkhf
s4 cvbmndfhu
s5 sdexyzhn其中path字段是CHAR型,path中的每个字母最多只会在内容中出现一次
现需要更新s1,s3,s5三条记录的path字段,使其内容中的'xyz'字母前的内容更新为'12345','xyz'字母及其后的内容保持不变
即更新后,s1,s3,s5的path内容为:
id path
----------------
s1 12345xyzsdfi
s3 12345xyzkhf
s5 12345xyzhn最坏的方法是分3条update语句,更新这三条记录
请问有没有更好的方法,可以提高update的效率呢? 包括在程序中进行字符串处理后在写入数据库的方法,只要效率高就可以了
急啊,请各位高手帮忙一下,非常谢谢!!!!!
id path
----------------
s1 sadfxyzsdfi
s2 uipxyzkl
s3 hjlxyzkhf
s4 cvbmndfhu
s5 sdexyzhn其中path字段是CHAR型,path中的每个字母最多只会在内容中出现一次
现需要更新s1,s3,s5三条记录的path字段,使其内容中的'xyz'字母前的内容更新为'12345','xyz'字母及其后的内容保持不变
即更新后,s1,s3,s5的path内容为:
id path
----------------
s1 12345xyzsdfi
s3 12345xyzkhf
s5 12345xyzhn最坏的方法是分3条update语句,更新这三条记录
请问有没有更好的方法,可以提高update的效率呢? 包括在程序中进行字符串处理后在写入数据库的方法,只要效率高就可以了
急啊,请各位高手帮忙一下,非常谢谢!!!!!
insert into @tb select 's1','sadfxyzsdfi'
insert into @tb select 's2','uipxyzkl'
insert into @tb select 's3','hjlxyzkhf'
insert into @tb select 's4','cvbmndfhu'
insert into @tb select 's5','sdexyzhn'update t set path=stuff(path,1,charindex('xyz',path)-1,'12345')
from @tb t where right(id,1)%2<>0select * from @tb where right(id,1)%2<>0id path
s1 12345xyzsdfi
s3 12345xyzkhf
s5 12345xyzhn
declare @T table (id varchar(2),path varchar(9))
insert into @T
select 's1','qwerxio' union all
select 's2','uipxkl' union all
select 's3','hjlxkhf' union all
select 's4','cvbmndfhu' union all
select 's5','sdexyhn'update @T set [path] = stuff([path],1,charindex('x',[path])-1,'12345') where id in ('s1','s2','s3')select * from @T/*
id path
---- ---------
s1 12345xio
s2 12345xkl
s3 12345xkhf
s4 cvbmndfhu
s5 sdexyhn
*/
drop table ta
GO
create table ta
(
id varchar(10),
path varchar(20)
)
GO
insert into ta(id,path)
select 'S1','sadfxyzsdfi'
union select 'S2','uipxyzkl'
union select 'S3','hjlxyzkhf'
union select 'S4','cvbmndfhu'
union select 'S5','sdexyzhn'
GO
select * from ta
GO
update ta set path = replace(path,left(path,charindex('xyz',path)-1),'12345') where id in('S1','S3','S5')
GO
insert into tb values('s1', 'sadfxyzsdfi')
insert into tb values('s2', 'uipxyzkl')
insert into tb values('s3', 'hjlxyzkhf')
insert into tb values('s4', 'cvbmndfhu')
insert into tb values('s5', 'sdexyzhn')
goupdate tb
set path = '12345' + substring(path , charindex('xyz',path) , len(path))
where id in ('s1' , 's3' , 's5')select * from tbdrop table tb/*
id path
---------- --------------------
s1 12345xyzsdfi
s2 uipxyzkl
s3 12345xyzkhf
s4 cvbmndfhu
s5 12345xyzhn(所影响的行数为 5 行)
*/
where id in ('s1','s3','s5')
create table tt (id varchar(2),path varchar(20))
insert into tt
select 's1','qwerxyzio' union all
select 's2','uipxkl' union all
select 's3','hjlxkhf' union all
select 's4','cvbmndfhu' union all
select 's5','sdexyhn'update tt set path = stuff(path,1,case when charindex('xyz',path,0)-1 = -1 then 0 else charindex('xyz',path,0)-1 end,'12345') from tt where id in ('s1','s3','s5')select * from tt