sql-求SQL語句,更新某列為某些字段出現的次數

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