在一个表中如果有相同记录的话,则进行修改操作,如果没有,则进行插入(多个条件),如何来实现?
表一 tblA
noid fItemId fQty
1 2 3
1 3 9
2 9 10
20 2 30
表二 tblB
noid fItemId fQty
1 2 90
1 3 20
1 6 1
2 9 10
20 1 2
20 2 30
现在要实现的功能是:
从表二中查询出来的记录,如果表二的noid , fItemId 与表一的相同(tblA.noid=tblB.noid and tblA.fitemid=tblB.fitemid),则把表二fQty的值加入到表一fQty中,如果没有相同的记录,则把表二的值插入到表一中,请问如何来实现?
操作后表一的记录如下
noid fItemId fQty
1 2 93
1 3 29
1 6 1
2 9 20
20 1 2
20 2 60
[解决办法]
update tblA
set
fQty=a.fQty+b.fQty
from
tblA a,tblB b
where
a.noid=b.noid and a.fItemId=b.fItemId
insert into tblA(noid,fItemId,fQty)
select
*
from
tblB b
where
not exists(select 1 from tblA where noid=b.noid and fItemId=b.fItemId)
[解决办法]
update a
set fQty=a.fQty+b.fQty
from tblA a,tblB b
where a.noid=b.noid and a.fItemId=b.fItemId
insert tblA
select * from tblB b where not exists(select 1 from tblA where noid=b.noid and fItemId=b.fItemId)
[解决办法]
--创建测试环境
create table tblA(noid int,fItemId int,fQty int)
create table tblB(noid int,fItemId int,fQty int)
--插入测试数据
insert tblA(noid,fItemId,fQty)
select '1 ', '2 ', '3 ' union all
select '1 ', '3 ', '9 ' union all
select '2 ', '9 ', '10 ' union all
select '20 ', '2 ', '30 '
insert tblB(noid,fItemId,fQty)
select '1 ', '2 ', '90 ' union all
select '1 ', '3 ', '20 ' union all
select '1 ', '6 ', '1 ' union all
select '2 ', '9 ', '10 ' union all
select '20 ', '1 ', '2 ' union all
select '20 ', '2 ', '30 '
--求解过程
update a
set a.fqty = a.fqty + b.fqty
from tbla a
join tblb b on a.noid = b.noid and a.fitemid= b.fitemid
insert tbla
select * from tblb b
where not exists(select 1 from tbla where b.noid = noid and fitemid= b.fitemid)
select * from tbla
order by noid,fitemid
--删除测试环境
drop table tblA,tblB
/*--测试结果
noid fItemId fQty
----------- ----------- -----------
1 2 93
1 3 29
1 6 1
2 9 20
20 1 2
20 2 60
*/
