update a
set
DoNotContact=2
from
#PMT a
where
exists(select * from #Ref where FistName=a.FistName and LastName=a.LastName and Zip=a.Zip group by FistName,LastName,Zip having count(*)=1)
set
DoNotContact=2
from
#PMT a
where
exists(select * from #Ref where FistName=a.FistName and LastName=a.LastName and Zip=a.Zip group by FistName,LastName,Zip having count(*)=1)
set DoNotContact=2
from
#PMT T,
(select FistName,LastName,Zip from #Ref
group by FistName,LastName,Zip
having count(*)=1
)A
where
T.FistName=A.FistName
and T.LastName=A.LastName
and T.Zip=A.Zip
create table #Ref(RefId int,FistName varchar(50),LastName varchar(100),Zip varchar(50),Updated int)
insert into #Ref select 15,'A1','B1','10000',0
insert into #Ref select 16,'liang','superrmm','10000',0
insert into #Ref select 17,'liang','superrmm','10000',0
create table #PMT(Id int,FistName varchar(50),LastName varchar(100),Zip varchar(50),DoNotContact int)
insert into #PMT select 1,'A1','B1','10000',0
insert into #PMT select 2,'A2','B3','10000',0
insert into #PMT select 3,'liang','superrmm','10000',0--更新语句
update T
set DoNotContact=2
from
#PMT T,
(select FistName,LastName,Zip from #Ref
group by FistName,LastName,Zip
having count(*)=1
)A
where
T.FistName=A.FistName
and T.LastName=A.LastName
and T.Zip=A.Zip--查看结果 select * from #PMT
ID FistName LastName Zip DoNotContact
------ --------- -------- ---------- -------------
1 A1 B1 10000 2
2 A2 B3 10000 0
3 liang superrmm 10000 0--删除测试环境
drop table #Ref,#PMT