Refresh the column to COUNT for specific values ​​in another column. SQL Server

Question

My table:

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

I need the result:

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

Found this Update the column value to the COUNT row for certain values ​​in the same table and tried it:

 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 gives an error:

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

Any idea how to do this in SQL Server (2014 Express).

Thanks in advance.

Answer 1

It should be Update..set...from . Try the following:

 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 

Result:

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

Demo in SQL Fiddle

Answer 2

Something like that

 UPDATE t SET t.cr = vv.c from MyTbl as t left outer join ( select count(*) as c , data from MyTbl group by data ) as vv on vv.data = t.data 

Answer 3

You can use count with the window function to find the amount of each group. use it

 ;WITH cte AS (SELECT Count(1)OVER(partition BY data) AS crc,* FROM MyTbl) UPDATE cte SET cr = crc 

SQLFIDDLE DEMO

Answer 4

Since you are using a version of SQL Server 2014, I would recommend the Windowed function used in the syntax of a self-join request type.

 update a set a.cr= bv from (select id, count(1) over(partition by data order by data) as v from myTbl) b join myTbl a on a.ID=b.id -- now see the result here select * from MyTbl 

Sql script for demo http://sqlfiddle.com/#!6/bc02f/4

Answer 5

 ;with x as ( SELECT Data , COUNT(*) AS cr FROM Table GROUP BY Data ) UPDATE t SET t.cr = x.cr FROM x INNER JOIN Table t ON x.data = t.data