update a
set
SUMQTY = isnull(b.QTY,0)
from
dt_Info a
left join
(select QTY=sum(QTY) from dt_Detail group by ) b
on
a.NUM = b.NUM
set
SUMQTY = isnull(b.QTY,0)
from
dt_Info a
left join
(select QTY=sum(QTY) from dt_Detail group by ) b
on
a.NUM = b.NUM
insert into #dt_Info select 1,null
insert into #dt_Info select 2,null
insert into #dt_Info select 3,nullcreate table #dt_Detail(NUM int,SNUM int,QTY int)
insert into #dt_Detail select 1,1,10
insert into #dt_Detail select 1,2,20
insert into #dt_Detail select 1,3,30
insert into #dt_Detail select 2,1,100
insert into #dt_Detail select 2,2,200
insert into #dt_Detail select 3,1,500
update a
set
SUMQTY = isnull(b.QTY,0)
from
#dt_Info a
left join
(select NUM,QTY=sum(QTY) from #dt_Detail group by NUM) b
on
a.NUM = b.NUMselect * from #dt_Info
drop table #dt_Info,#dt_Detail
go
create table #dt_Info(NUM int,SUMQTY int)
insert into #de_Info select 1,null
insert into #de_Info select 2,null
insert into #de_Info select 3,nullcreate table #dt_Detail(NUM int,SNUM int,QTY int)
insert into #dt_Detail select 1,1,10
insert into #dt_Detail select 1,2,20
insert into #dt_Detail select 1,3,30
insert into #dt_Detail select 2,1,100
insert into #dt_Detail select 2,2,200
insert into #dt_Detail select 3,1,500
--执行update操作
update a
set
SUMQTY = isnull(b.QTY,0)
from
#dt_Info a
left join
(select QTY=sum(QTY) from #dt_Detail group by ) b
on
a.NUM = b.NUM
--查看结果
select * from #dt_Info
/*
NUM SUMQTY
------------------
1 60
2 300
3 500
*/
--删除测试数据
drop table #dt_Info,#dt_Detail