一个表中用sum计出总和,然后更新到另一个表中,SQL语句如何写?

[CsdnBoom]

MSSQl2000
请教一条语句如何把'表1'中相同的'GoodsID'的'Qty'之和更新到'表2'中的'Qty'

--------------
表1
GoodsID Qty
001 10
001 15
001 20
002 10
002 20
003 10
003 30

表2
GoodsID Qty
001
002
003
------------

[liangCK]

update tb2
set qty=(select sum(qty) from tb1 where tb2.goodsid=tb1.goodsid)

[Limpire]

--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (GoodsID varchar(3),Qty int)
insert into #1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (GoodsID varchar(3),Qty varchar(50))
insert into #2
select '001',null union all
select '002',null union all
select '003',null

update a set a.Qty=b.Qty from #2 a join (select GoodsID,sum(Qty)Qty from #1 group by GoodsID) b on a.GoodsID=b.GoodsID

select * from #2

/*
GoodsID Qty
------- -------
001     45
002     30
003     40
*/

[dawugui]

create table tb1 (GoodsID varchar(3),Qty int)
insert into tb1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
create table tb2 (GoodsID varchar(3),Qty varchar(50))
insert into tb2
select '001',null union all
select '002',null union all
select '003',null
go

update tb2 set qty = (select sum(qty) from tb1 where tb1.goodsid = tb2.goodsid)

--或者

update tb2 set qty = isnull((select sum(qty) from tb1 where tb1.goodsid = tb2.goodsid),0)

select * from tb2

drop table tb1,tb2

/*
GoodsID Qty                                                
------- -------------------------------------------------- 
001     45
002     30
003     40

（所影响的行数为 3 行）
*/

[TNT_1st_excellence]

update 表2
  set Qty=(
           select 
                  sum(Qty) 
             from 
                  表1 
            where 
                  表2.GoodsID = 表1.GoodsID)

[-狙击手-]

create table tb1 (GoodsID varchar(3),Qty int)
insert into tb1
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30
create table tb2 (GoodsID varchar(3),Qty int)
insert into tb2
select '001',null union all
select '002',null union all
select '003',null
go

update a 
set a.qty = b.s
from tb2 a,(select goodsid,sum(qty) as s from tb1 group by goodsid) b
where a.goodsid = b.goodsid


select * from tb2

drop table tb1,tb2

/*

GoodsID Qty         
------- ----------- 
001     45
002     30
003     40

（所影响的行数为 3 行）
*/

[中国风]

update t2
set Qty=isnull(Sum(t1.Qty),0)--t1不存在的GoodsID时为0
from t1 right join t2 on t1.GoodsID=t2.GoodsID

[pt1314917]

update 表2 set Qty=b.Qtys from 表2 a,(select goodsid,sum(qty)qtys from 表1 group by goodsid) b where a.goodsid=b.goodsid 

[wangxuelid]

if object_id('tb') is not null
drop table tb

create table tb (GoodsID varchar(3),Qty int)
insert into tb
select '001',10 union all
select '001',15 union all
select '001',20 union all
select '002',10 union all
select '002',20 union all
select '003',10 union all
select '003',30

if object_id('tbb') is not null
drop table tbb

create table tbb (GoodsID varchar(3),Qty varchar(50))
insert into tbb
select '001',null union all
select '002',null union all
select '003',null



select * from tbb
select * from tb


update  b set Qty=(select sum(Qty) from  tb  where GoodsID=b.GoodsID ) from tbb  b

[wangxuelid]

/*

GoodsID Qty         
------- ----------- 
001     45
002     30
003     40

（所影响的行数为 3 行）
*/

[ojuju10]

update a set qty=(select sum(qty) from tab1 where a.goodsid=goodsid) from tab2 a

[pengxuan]

update t2 set qty=(select sum(qty) from t1 where t1.GoodsID=t2.GoodsID) from t2

[conan304]

update a
set Qty=(select sum(qty) from 表1 where goodsid=a.goodsid group by goodsid)
from 表2 a