sql-server - 将列更新为另一列中特定值的 COUNT 行。 SQL服务器

Question

我的 table 是:

|ID |data | cr |
| 1 | AAA |    |
| 2 | AAA |    |
| 3 | AAA |    |
| 4 | BBB |    |
| 5 | CCC |    |
| 6 | BBB |    |

我需要结果:

|ID |data | cr |
| 1 | AAA | 3  |
| 2 | AAA | 3  |
| 3 | AAA | 3  |
| 4 | BBB | 2  |
| 5 | CCC | 1  |
| 6 | BBB | 2  |

找到这个Update a column value to the COUNT of rows for specific values in same table并尝试过:

UPDATE MyTbl a, 
        (SELECT data,COUNT(*) cnt 
            FROM MyTbl 
          GROUP BY data) b
    SET a.cr = b.cnt 
  WHERE a.data= b.data

SQL Server 给出错误:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'b'.

知道如何在 SQL Server (2014 Express) 中执行此操作。

提前致谢。

Answer 1

应该是Update..set...from。试试这个:

update a
set a.cr=b.cnt
from MyTbl a join
     (SELECT data,COUNT(*) cnt 
            FROM MyTbl 
          GROUP BY data) b on a.data=b.data

结果:

ID  data    cr
--------------
1   AAA     3
2   AAA     3
3   AAA     3
4   BBB     2
5   CCC     1
6   BBB     2

演示于SQL Fiddle