求SQL语句,更新某列为某些字段出现的次数

求SQL语句提示...

表名 Test, 把Num设置为Col_A的个数.

原始数据:
Col_A, Num
123 0
123 0
124 0
125 0

更新后数据
原始数据:
Col_A, Num
123 2
123 2
124 1
125 1

SQL语句:
update TEST set Num=(select COUNT(Col) from TEST A where TEST.Col=A.Col group by A.Col)

谢谢热心的朋友..

亲测通过，请采纳。

create table t1(
col_A int,
num int
);

insert into t1 values(101,0),(101,0),(102,0),(102,0),(103,0),(103,0),(103,0);

UPDATE t1
INNER JOIN
(SELECT
col_A, COUNT(col_A) AS count
FROM
t1
GROUP BY col_A
ORDER BY col_A) AS t ON t.col_A = t1.col_A
SET
num = t.count;

select * from t1;

这问题有点难度，不会回答

UPDATE Test SET Num=(SELECT Count(Col_A) FROM Test Where Col_A='123');
貌似这个每次只能更新一行，你可以读出所有数据，然后循环更新。

我是这么解决的。先将结果存入一个临时表中，然后再赋值到当前表，最后删除临时表。

CREATE TEMPORARY TABLE tmp_table select Col_A, count(Col_A) as num from TestA group by Col_A;
update TestA set NUM = (select num from tmp_table where Col_A = TestA.Col_A);
drop table tmp_table;

 UPDATE TEST
   SET NUM = T.CNT
    FROM (SELECT T0.COL_A,COUNT(T0.*) AS CNT FROM TEST T0 GROUP BY T0.COL_A) T
WHERE TEST.COL_A = T.COL_A

大家好,已经有人解答了..
SQL Server 下.
正解是:
update TEST set Num=(select COUNT(Col) from TEST A where TEST.Col=A.Col group by A.Col)