现在有两张表:
A表:字段有,[userid int,pid int,did int,byear int,m1 int,m2 int ,m3 int,int,money decimal]
B表:字段有,[userid int ,pid int , did int , byear int ,m1 int,money decimal]
现在我要做的效果是:
如果B表中userid,pid,did这一组合在A表中没有的,那就将此记录插入到A表中,
如果B表中的userid,pid,did这一组合在A表中已经存在了,那就将此记录更新到A表中
请大家数据SQL语句会写的,帮忙给出这个正确的SQL语句,谢谢
我现在已经有的做法是:
这是更新语句,可以得到正确结果:
update a set m1=b.m1,money=b.money from b where b.userid=a.userid and b.pid=a.pid and b.did=a.did and b.byear=a.byear这是插入语句,但无法得到想要的结果:
insert into a (userid,pid,did,byear,m1,money) select b.userid,b.pid,b.did,b.byear,b.m1,b.money from b
where not exists (select b1.userid,b1.pid,b1.did,b1.byear,b1.m1,b1.money from b as b1 ,a as a1
where b1.userid=a1.userid and b1.pid=a1.pid and b1.did=a1.did)
A表:字段有,[userid int,pid int,did int,byear int,m1 int,m2 int ,m3 int,int,money decimal]
B表:字段有,[userid int ,pid int , did int , byear int ,m1 int,money decimal]
现在我要做的效果是:
如果B表中userid,pid,did这一组合在A表中没有的,那就将此记录插入到A表中,
如果B表中的userid,pid,did这一组合在A表中已经存在了,那就将此记录更新到A表中
请大家数据SQL语句会写的,帮忙给出这个正确的SQL语句,谢谢
我现在已经有的做法是:
这是更新语句,可以得到正确结果:
update a set m1=b.m1,money=b.money from b where b.userid=a.userid and b.pid=a.pid and b.did=a.did and b.byear=a.byear这是插入语句,但无法得到想要的结果:
insert into a (userid,pid,did,byear,m1,money) select b.userid,b.pid,b.did,b.byear,b.m1,b.money from b
where not exists (select b1.userid,b1.pid,b1.did,b1.byear,b1.m1,b1.money from b as b1 ,a as a1
where b1.userid=a1.userid and b1.pid=a1.pid and b1.did=a1.did)
insert into a (userid,pid,did,byear,m1,money)
select b.userid,b.pid,b.did,b.byear,b.m1,b.money
from b left join a on
b.userid=a.userid and b.pid=a.pid and b.did=a.did and b.byear=a.byear
where a.userid is null
insert into a (userid,pid,did,byear,m1,money)
select b.userid,b.pid,b.did,b.byear,b.m1,b.money
from b as b1
where not exists (select 1 from a as a1
where b1.userid=a1.userid and b1.pid=a1.pid and b1.did=a1.did)
insert into a (userid,pid,did,byear,m1,money)
select b.userid,b.pid,b.did,b.byear,b.m1,b.money
from b
where not exists (
select 1
from a
where b.userid=a.userid and b.pid=a.pid and b.did=a.did)
set byear=b.byear,m1=b.m1,[money]=b.[money]
where
b on a.userid=b.userid and a.pid=b.pid and a.did=b.didinsert a(userid,pid,did,m1,[money])
select userid,pid,did,m1,[money] from b where checksum(userid,pid,did)not in(select checksum(userid,pid,did) from a)
not exists ( select 1 ………………如果条件符合,那么上面的的语句取反(对于上面的语句来说不符合。)。你仔细看看exists 的帮助吧
insert into a (userid ,pid, did, byear,m1,money)
select userid,pid,did,0,0,0 from a not exists(select * from b where a.userid=b.userid
and a.pid=b.pid and a.did=b.did)
update a set a.byear=b.byear and a.m1=b.m1 and a.money=b.money
from a inner join b on .userid=b.userid and a.pid=b.pid and a.did=b.did