求助，关于表批量更新的基础问题

[kmlzkma]

a表：
id hits
1 0
2 0
3 0
.....

b表
id hits
1 1
3 5
......

c表
id hits
1 10
2 25
......




b，c表是a表子集，有很多的类似b,c的表，a表是个汇总，是其它所有表的一个总和。a表大约10万条记录，其它表记录数略少点。

现在要把其它表汇总到a表，请问如何写。

[-狙击手-]

update a
set hits = (select sum(hits) from b表 where id = a.id) +
           (select sum(hits) from c表 where id = a.id) 
from a表 a

[wzy_love_sly]

update a set hits=sum(b.hits)+sum(c.hits)
from a left join b on a.id=b.id
left join c on a.id=c.id
group by a.id

[kk19840210]

create table #a (id int,hits int)

create table #b (id int,hits int)

create table #c (id int,hits int)

insert into #a values(1,0)
insert into #a values(2,0)
insert into #a values(3,0)

insert into #b values(1,1)

insert into #b values(3,5)

insert into #c values(1,10)
insert into #c values(2,25)





update #a set hits=a.hits from #a inner join (select id,hits=sum(hits) from (select * from #b union all select * from #c) b group by b.id) a on #a.id=a.id

select * from #a



id          hits
----------- -----------
1           11
2           25
3           5

(3 行受影响)

[dobear_0922]

update a
set hits = (select sum(hits) from b表 where id = a.id) + (select sum(hits) from c表 where id = a.id) 
from a表 a

[wzy_love_sly]

declare @a table (id int,hits int)
insert into @a select 1,0
insert into @a select 2,0
insert into @a select 3,0

declare @b table (id int,hits int)
insert into @b select 1,3
insert into @b select 3,5

declare @c table (id int,hits int)
insert into @c select 1,10
insert into @c select 2,25

update   @a   set   hits=
(select isnull(sum(hits),0) from @b where id=a.id)+
(select isnull(sum(hits),0)  from @c where id=a.id)
from   @a a  


select * from @a

id hits
1 13
2 25
3 5

上面错了，楼主别看

[kmlzkma]

回复1楼，报错，列不允许有空值，b表是a表的真子集，是不是因为a表有的id，b表中没有，所以报错。还有不明白为什么用sum（），id是主键，

[changjiangzhibin]

study