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)
現(xiàn)在b表有數(shù)據(jù)a,b
我現(xiàn)在的需求是update b set b=count(*) from
(
select a,count(b)
from a
group by a
)
where a.a=b.a
請(qǐng)問怎么寫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

恩,就是這樣做的,不能用子查詢

用動(dòng)態(tài)的SQL可以嗎?