請教一個復雜的Update問題
有表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)