两张表合并的sql语句的写法

rareliu 2008-11-08

现在有两张表:
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)

lonewan 2008-11-26

建立一张没什么约束的临时表TAB_TEMP，把俩张表A、表B的数据都插入进去

然后去掉重复数据插入到目标表里

martri 2008-11-26

insert into a (userid,pid,did,byear,m1,money) select b.userid,b.pid,b.did,b.byear,b.m1,b.money from b 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)

yaodongliang 2009-12-02

if exists(select * from a,b where a.userid=b.userid and a.pid=b.pid and a.did=b.did
)
update a set m1=b.m1,money=b.money from b where a.userid=b.userid and a.pid=b.pid and a.did=b.did

else
insert into a(userid,pid,did,m1,money) select * from b

hwhuang 2009-12-02

给你一个方法，用最笨重的方法存储过程写很快就出来了：
procedure P_COPY_TBL()AS
cursor cur_tbl_info as
Select *
from b;
v_tbl_info Cur_tbl_info%RowType;
v_count number;
BEGIN
Open Cur_tbl_info;
Loop
Fetch Cur_tbl_info Into v_tbl_info;
Exit when Cur_tbl_info%NotFound;

select count(1) into V_count
from a where a.userId = v_tbl_info.userId and a.pId = v_tbl_info.pId and a.dId = v_tbl_info.dId;
if (v_count >0) then
update a set where a.userId = v_tbl_info.userId and a.pId = v_tbl_info.pId and a.dId = v_tbl_info.dId;
else
insert into a(userId,pId,dId)values(v_tbl_info.userId,
v_tbl_info.pId,v_tbl_info.dId)
end if;
END P_COPY_TBL;

hwhuang 2009-12-02

有时候最笨的方法是最好的方法。。。

ministerosy 2009-12-21

我不认同楼上的存储过程

新的试试

create or replace procedure pro_cur_only_test as
cursor  cur_b_info is select * from b;
v_count number;

begin
for cur_rowtype_b_info in cur_b_info loop
select count(*) into v_count from a 
  where a.userId=cur_rowtype_b_info.userId;
if(v_count>0) then
update ……
else
insert ……
end if;
end loop;
end pro_cur_only_test;

idolformyself 2010-01-18

可以使用merge into啊...

shupili141005 2010-02-01

如果可以，考虑直接copy数据到数据库，简单实用
请问你用哪个数据库（Oracle、MySQL？）

shupili141005 2010-02-05

从你插入的SQL语句，表结构很复杂。如果是我，我会重构表结构

发表回复

>>返回群组首页

两张表合并的sql语句的写法

相关讨论

相关资源推荐