帮忙一条SQL语句

[smiler007]

表中的记录
字段1 字段2
aaa
aaa
bbb
ccc
aaa
bbb
---------------
根据字段1来更新字段2，字段2放字段1的记录数，更新后的表
字段1 字段2
aaa 3
aaa 3
bbb 2
ccc 1
aaa 3
bbb 2
-----------------
用一条SQL语句实现....怎么写...

[duanduan1122]

1.
select 字段1,count(字段1) sum into #temp
from yourtable

2.
update t1
set 字段2=t2.sum
from yourtable t1 join #temp t2
on t1.字段1=t2.字段1

[hsj20041004]

update A set A.字段2=B.num
from tablename A inner join
(select 字段1 ,count(*) as num from tablename group by 字段1 ) B
on A.字段1=B.字段1

[pbsql]

update 表 set 字段2=(select count(*) from 表 a where a.字段2=表.字段2)

[pbsql]

写错了：
update 表 set 字段2=(select count(*) from 表 a where a.字段1=表.字段1)

[了缘]

update table set 2=a.num
from (select 1,count(1) as num from table
group by 1) a
where table.1=a.1

[duanduan1122]

修改一下：
1.
create table yourtable
(字段1 varchar(20),
字段2 int)
2.
insert yourtable(字段1)
select 'aaa'
union all select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'aaa'
union all select 'bbb'

3.
select * from yourtable



4.
select 字段1,count(字段1) sum into #temp
from yourtable
group by 字段1

5..
update t1
set 字段2=t2.sum
from yourtable t1 join #temp t2
on t1.字段1=t2.字段1

6.
select * from yourtable


7。结果

aaa 3
aaa 3
bbb 2
ccc 1
aaa 3
bbb 2

[Erice]

Update tablesname a set a.z1=
(select count(z1) from tablename where z1=a.z1 group by z1)

ORACLE测试过！！ 不知道 SQL SERVER可以吗？