update问题

ARISYANG

create table a(
a varchar(10) null,
b number(5) null
)
insert into a values ('a','1')
insert into a values ('a','2')
insert into a values ('a','3')
insert into a values ('b','4')
insert into a values ('b','5')

create table b(
a varchar(10) null,
b number(5) null
)
insert into b(a) (select distinct a from b)
现在b表有数据a,b
我现在的需求是update b set b=count(*) from
(
select a,count(b)
from a
group by a
)
where a.a=b.a
请问怎么写SQL?

jasonshan

select a,count(b) as b into #tmp1 from a group by a
update b set b.b=#tmp1.b from #tmp1 where b.a=#tmp1.a

ARISYANG

恩,就是这样做的,不能用子查询

用动态的SQL可以吗?