update语句

[叔到用时方恨嫂]

表A
id    qty
1      20
1      30
1      40
2      12
2      20
2      30
...
表B    qty
1      30
2      40
...

首先表A分组得到
id    qty
1     20+30+40 = 90
2     12+20+30 = 62
然后通过id修改表B的qty（之前表B的qty不正确），
得到表B:
id    qty
1     90
2     62

用一个update语句实现

[AcHerat]

update b
set b.qty = c.qty
from b,(select id,sum(qty) as qty from a group by id) c
where b.id = c.id

[中国风]

update b
set qty=a.qty
from 
(select ID,qty=sum(qty) from a group by ID)a
inner join b on a.ID=b.ID

[中国风]

或这样用

update b
set qty=a.qty
from (select ID,qty=sum(qty) from a group by ID)a
where a.ID=b.ID

[苦苦的潜行者]

use test
go
---(苦苦的潜行者)---
if object_id('t1') is not null and object_id('t2') is not null
drop table t1,t2
go
create table t1(id int,qty int)
go
insert t1
select 1,20 union all
select 1,30 union all
select 1,40 union all
select 2,12 union all
select 2,20 union all
select 2,30 
go
create table t2 (id int ,qty int)
insert t2
select 1,30 union all
select 2,40 
go
update a set a.qty=b.qty from t2 a,
(select id,sum(qty) as qty from t1 group by id) b
go
select * from t2
/*
id     qty
---    ---
1	90
2	62

*/
go
drop table t1,t2

[中国风]

测个例子

--> --> (Roy)生成測試數據
 
if not object_id('A') is null
	drop table A
Go
Create table A([id] int,[qty] int)
Insert A
select 1,20 union all
select 1,30 union all
select 1,40 union all
select 2,12 union all
select 2,20 union all
select 2,30
Go
--> --> (Roy)生成測試數據
 
if not object_id('B') is null
	drop table B
Go
Create table B([ID] int,[qty] int)
Insert B
select 1,30 union all
select 2,40
Go
update b
set qty=a.qty
from (select ID,qty=sum(qty) from a group by ID)a
where a.ID=b.ID
go

select * from b
/*
ID	qty
1	90
2	62
*/

[苦苦的潜行者]

晕死,楼上忘了加一个重要的条件
修正为

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

[pengxuan]

if object_id('A','U') is not null
   drop table A
go
create table A
(
 id int ,
 qty int
)
go
insert into A
select 1,20 union all
select 1,30 union all
select 1,40 union all
select 2,12 union all
select 2,20 union all
select 2,30
go
if object_id('B','U') is not null
   drop table B
go
create table B
(
 id int,
 qty int
)
go
insert into B
select 1,30 union all
select 2,40
go
update B set qty=t2.qty from B t1 inner join (select id,qty=sum(qty) from A group by id) t2 on t1.id=t2.id
go
select * from B
/*
id          qty
----------- -----------
1           90
2           62

(2 行受影响)


*/

[苦苦的潜行者]

[Quote=引用 5 楼 roy_88 的回复:]

SQL code
测个例子

--> --> (Roy)生成測試數據

if not object_id('A') is null
drop table A
Go
Create table A([id] int,[qty] int)
Insert A
select 1,20 union all
select 1,30 union all
select 1,40 union all
s……
[/Quote]

Roy插楼好快...

[苦苦的潜行者]

...这楼插得...