update 2
set d=1
where a in (select a from 1)

go

update 1
set d=0

------解决方案--------------------
SQL code

update t2
set d=1
from 
    Template_ZDZ_Z_Rec t2
join
    StaNum_ZDZ_Rec_all t1 on t2.a=t1.a
where
    t1.b='sx' and t1.c in('正常','缺报')

------解决方案--------------------
SQL code

--"正常”和“缺报 另一个意思为同时包含正常”和“缺报
update t2
set d=1
from 
    Template_ZDZ_Z_Rec t2
where
    exists(select 1 from StaNum_ZDZ_Rec_all where a.=t2.a and c='正常')
and
    exists(select 1 from StaNum_ZDZ_Rec_all where a.=t2.a and c='缺报')

------解决方案--------------------
SQL code
update a set a.d=1
from Template_ZDZ_Z_Rec a join StaNum_ZDZ_Rec_all b on a.a=b.a
where b.b='sx' and b.c in ('正常','缺报')

------解决方案--------------------
直接查不匹配的就是了

select y.a,y.d from t2 y
left join t1 x
	on x.a=y.a
	where x.b='sx' and charindex(x.c,'正常缺报')>0
		and x.a isnull

------解决方案--------------------
SQL code

create table StaNum_ZDZ_Rec_all(a站号 int, b日期 int,c报文状态 varchar(50))
insert into StaNum_ZDZ_Rec_all select 50953 ,       2007      ,     '正常' 
insert into StaNum_ZDZ_Rec_all select 50850       , 2007   ,        '缺报' 
insert into StaNum_ZDZ_Rec_all select 50772       , 2007   ,        '缺报' 
insert into StaNum_ZDZ_Rec_all select 50742       , 2007  ,         '正常' 
insert into StaNum_ZDZ_Rec_all select 50765       , 2007  ,         '好' 


create table Template_ZDZ_Z_Rec (a站号 int, d接收状态 int)  
insert into Template_ZDZ_Z_Rec select 50953  ,         0 
insert into Template_ZDZ_Z_Rec select 50850  ,         0 
insert into Template_ZDZ_Z_Rec select 50772  ,         0 
insert into Template_ZDZ_Z_Rec select 50742  ,         0 
insert into Template_ZDZ_Z_Rec select 50986  ,         0 
insert into Template_ZDZ_Z_Rec select 50812  ,         0 

select b.a站号 from Template_ZDZ_Z_Rec b where 
a站号 not in(select a站号 from StaNum_ZDZ_Rec_all b1 where b1.c报文状态 in ('正常','缺报'))