update tt set tt.LXBH=t1.LXBH, tt.LBBH=t1.LBBH from (select * from a where (DATEDIFF([day], FFRQ, getdate()) = 0)) t1,njgfw..grocertogo.b tt where tt.id =t1.id --插入不存在的 insert into njgfw..grocertogo.b select * from a WHERE (DATEDIFF([day], FFRQ, getdate()) = 0) and a.BS not in (select BS from njgfw..grocertogo.b where (DATEDIFF([day], FFRQ, getdate()) = 0) )
update错了 update tt set tt.LXBH=t1.LXBH, tt.LBBH=t1.LBBH, tt.FFRQ=t1.FFRQ from (select * from a where (DATEDIFF([day], FFRQ, getdate()) = 0)) t1,njgfw..grocertogo.b tt where tt.id =t1.id 就可以了,如果FFRQ=t1.FFRQ不加,下面就会多加上
set tt.LXBH=t1.LXBH,
tt.LBBH=t1.LBBH
from (select * from a where (DATEDIFF([day], FFRQ, getdate()) = 0)) t1,njgfw..grocertogo.b tt
where tt.id =t1.id
--插入不存在的
insert into njgfw..grocertogo.b
select *
from a
WHERE (DATEDIFF([day], FFRQ, getdate()) = 0)
and a.BS not in
(select BS from njgfw..grocertogo.b
where (DATEDIFF([day], FFRQ, getdate()) = 0)
)
id lxbh lbbh ffrq
1 1 1 2004-7-28
2 1 1 2004-7-28
a表:
id lxbh lbbh ffrq
1 1 1 2004-7-31
2 1 1 2004-7-31
3 1 1 2004-7-31
4 1 1 2004-7-31经过上面的处理:
b表为:
id lxbh lbbh ffrq
1 1 1 2004-7-28
2 1 1 2004-7-28
1 1 1 2004-7-31
2 1 1 2004-7-31
3 1 1 2004-7-31
4 1 1 2004-7-31不对呀
update tt
set tt.LXBH=t1.LXBH,
tt.LBBH=t1.LBBH,
tt.FFRQ=t1.FFRQ
from (select * from a where (DATEDIFF([day], FFRQ, getdate()) = 0)) t1,njgfw..grocertogo.b tt
where tt.id =t1.id
就可以了,如果FFRQ=t1.FFRQ不加,下面就会多加上