CRP_System_InterfaceInfo表CRP_Business_AfaceSort表2张数据表,其中CRP_Business_AfaceSort表中的sort字段中的值对应的是CRP_System_InterfaceInfo表中id的值,现在要把CRP_Business_AfaceSort表中的sort字段中的值改为CRP_System_InterfaceInfo表中id对应的i_name,以下是代码while(exists (select * from CRP_Business_AfaceSort,CRP_System_InterfaceInfo where charindex(ltrim(CRP_System_InterfaceInfo.id),ltrim(CRP_Business_AfaceSort.sort))>0))
update CRP_Business_AfaceSort
set sort= replace(sort,CRP_System_InterfaceInfo.id,CRP_System_InterfaceInfo.I_Name)
from CRP_System_InterfaceInfo
where charindex(ltrim(CRP_System_InterfaceInfo.id),ltrim(CRP_Business_AfaceSort.sort))>0
执行后结果不正确。。
--有点小问题,我帮你改改,稍等
declare @a table (id int,col varchar(1))
insert into @a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 10,'e' union all
select 11,'f' union all
select 12,'g' union all
select 14,'h'declare @b table (id int,col varchar(15))
insert into @b
select 1,'1,10,11,14' union all
select 2,'1,2,10' union all
select 3,'12,14' union all
select 4,'2,3' union all
select 5,'12,13'select * from @bwhile(exists (select * from @a a,@b b where charindex(','+ltrim(a.id)+',',+','+ltrim(b.col)+',') >0))
update @b
set col= replace(b.col,a.id,a.col)
from @b b,@a a
where charindex(','+ltrim(a.id)+',',+','+ltrim(b.col)+',') >0select * from @b/*
id col
----------- ---------------
1 1,10,11,14
2 1,2,10
3 12,14
4 2,3
5 12,13id col
----------- ---------------
1 a,a0,aa,a4
2 a,b,a0
3 g,h
4 b,c
5 g,13
*/
declare @a table (id int,col varchar(1))
insert into @a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 10,'e' union all
select 11,'f' union all
select 12,'g' union all
select 14,'h'declare @b table (id int,col varchar(15))
insert into @b
select 1,'1,10,11,14' union all
select 2,'1,2,10' union all
select 3,'12,14' union all
select 4,'2,3' union all
select 5,'16,13'select * from @bwhile(exists (select * from @a a,@b b where charindex(','+ltrim(a.id)+',',+','+ltrim(b.col)+',') >0))
begin
update @b
set col= replace(','+b.col+',',','+ltrim(a.id)+',',','+ltrim(a.col)+',')
from @b b,@a a
where charindex(','+ltrim(a.id)+',',+','+ltrim(b.col)+',') >0
update @b
set col=substring(col,2,len(col)-2)
where left(col,1)=','endselect * from @b /*
--更新前
id col
----------- ---------------
1 1,10,11,14
2 1,2,10
3 12,14
4 2,3
5 16,13--更新后
id col
----------- ---------------
1 a,e,f,h
2 a,b,e
3 g,h
4 b,c
5 16,13
*/
update CRP_Business_AfaceSort
set sort= replace(sort,CRP_System_InterfaceInfo.id,CRP_System_InterfaceInfo.I_Name)
from CRP_System_InterfaceInfo
where charindex(','+ltrim(CRP_System_InterfaceInfo.id)+',',','+ltrim(CRP_Business_AfaceSort.sort)+',')>0
加上了。也不行呢