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

Question

求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)

谢谢热心的朋友..

Answer 1

亲测通过，请采纳。

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;