有表Comp
A B
SA01
SA02
SA03
表Shop
C D
SA02;SA03 1
SA01;SA04 2
現在要求Update 表Comp的B字段,
當表Comp中A字段存在于表Shop的C字段中,就用D來Update.(例如SA01存在于SA01;SA04)
理論得出結果
表Comp
A B
SA01 2
SA02 1
SA03 1
謝謝!
------解决方案--------------------
update C set B=S.D from Comp C,Shop S where charindex( '; '+C.A+ '; ', '; '+S.C+ '; ')> 0
------解决方案--------------------
update comp set b=t.d from shop t where charindex(a,c)> 0
------解决方案--------------------
Create Table #comp
(a char(4),b int)
Insert #COMP Select 'SA01 ',0
Union All Select 'SA02 ',0
Union All Select 'SA03 ',0
CREATE TABLE #Shop
(C CHAR(10),D INT)
Insert #SHOP Select 'SA02;SA03 ',1
Union All Select 'SA01;SA04 ',2
update C set B=S.D from #Comp C,#Shop S where charindex(C.A,S.C)> 0
SELECT * FROM #COMP
------解决方案--------------------
create table Comp(A varchar(10), B int)
insert comp(a)
select 'SA01 '
union select 'SA02 '
union select 'SA03 '
create table Shop(C varchar(10),D int)
insert shop
select 'SA02;SA03 ',1
union select 'SA01;SA04 ',2
update comp set b=t.d from shop t where charindex(a,c)> 0
select * from comp
drop table comp,shop
------解决方案--------------------
update A set A.b=B.d from shop B, comp A where charindex( '; '+A.a+ '; ', '; '+B.c+ '; ')> 0
------解决方案--------------------
update Comp set B = (select D from Shop where charindex(Comp.A,Shop.C)> 0)