update b set kc = kc -isnull((select sum(cn) from a where aid = bid and orderid = 6),0)
update b set kc = kc -isnull((select sum(cn) from a where aid = b.bid and orderid = 6),0)
create table a(aid int,cn int,orderid int) insert into a select 1,2,6 union all select 2,1,6 union all select 3,4,6 union all select 4,5,7create table b(bid int,kc int) insert into b select 1,200 union all select 2,200 union all select 3,200 union all select 4,200update b set kc = kc - a.cn from a where a.aid = b.bid and a.orderid = 6 select * from a select * from b /* (所影响的行数为 3 行)aid cn orderid ----------- ----------- ----------- 1 2 6 2 1 6 3 4 6 4 5 7(所影响的行数为 4 行)bid kc ----------- ----------- 1 198 2 199 3 196 4 200(所影响的行数为 4 行) */ drop table a drop table b
update b set kc=kc -isnull(c.num,0) from b,(select aid, sum(cn)as num from a group by aid where orderid = 6) c where b. bid =c.aid
update b表 set kc=kc-(select kc from a,b where 订单id=6 and a.id=b.id)
update b set kc=kc+a.cn from a where a.aid=b.bid and orderid=6
set kc = kc -isnull((select sum(cn) from a where aid = bid and orderid = 6),0)
set kc = kc -isnull((select sum(cn) from a where aid = b.bid and orderid = 6),0)
insert into a
select 1,2,6 union all
select 2,1,6 union all
select 3,4,6 union all
select 4,5,7create table b(bid int,kc int)
insert into b
select 1,200 union all
select 2,200 union all
select 3,200 union all
select 4,200update b set kc = kc - a.cn from a where a.aid = b.bid and a.orderid = 6
select * from a
select * from b
/*
(所影响的行数为 3 行)aid cn orderid
----------- ----------- -----------
1 2 6
2 1 6
3 4 6
4 5 7(所影响的行数为 4 行)bid kc
----------- -----------
1 198
2 199
3 196
4 200(所影响的行数为 4 行)
*/
drop table a
drop table b
kc=kc-(select kc from a,b
where 订单id=6 and a.id=b.id)