求条更新语句!

[ado_cc]

create table t1(ID int,qty int)   ---入库表
insert into t1 select 184147,50
insert into t1 select 184214,198
insert into t1 select 184280,2
insert into t1 select 184322,80
create table t2(ID int,qty int)   ---库存表
insert into t2 select 184147,50
insert into t2 select 184214,198
insert into t2 select 184322,80

--求条更新语句,用库存表加上入库表的qty,如果入库表里有的ID,而在库存表里没有的话,就在库存表里增加一条记录


/* 
最后得倒库存表的数据
ID	qty
184147	100
184214	396
184322	160
184280	2  */

drop table t1,t2

[SQL77]

插入不存在的，更新存在的，用触发器维护一下吧

[-晴天]

create table t1(ID int,qty int)   ---入库表
insert into t1 select 184147,50
insert into t1 select 184214,198
insert into t1 select 184280,2
insert into t1 select 184322,80
create table t2(ID int,qty int)   ---库存表
insert into t2 select 184147,50
insert into t2 select 184214,198
insert into t2 select 184322,80
go
update a set qty=a.qty+b.qty from t2 a inner join t1 b on a.id=b.id
insert into t2 
select id,qty from t1 a where not exists(select 1 from t2 where id=a.id)
select * from t2
go
drop table t1,t2
/*
ID          qty
----------- -----------
184147      100
184214      396
184322      160
184280      2

(4 行受影响)

*/

[GoAwayZ]

update a set a.qty=a.qty+b.qty
from t2 a,t1 b


insert into t2
 select * form t1 where id not in(select id from t1)

[AcHerat]

--分两步

insert into t2
select id,sum(qty) as qty
from t1
where id not in (select id from t2)
group by id


update a
set a.qty = a.qty + b.qty
from t2 a join (select id,sum(qty) as qty from t1 group by id)b on a.id = b.id

[cd731107]

if exists (select * from t2 where id=184280)
   update t2 set qty=qty+a.qty from t1 where t2.id=t1.id and t1.id=184280
else
   insert t2 select * from t1 where id=184280