Update column to the COUNT of rows for specific values in another column. SQL Server

Question

My Table is:

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

I need 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 a column value to the COUNT of rows for specific values in 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 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.

Raging Bull · Accepted Answer · 2015-08-18T12:24:56.920

5

It should be Update..set...from. Try this one:

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

edited Aug 18 '15 at 12:24

answered Aug 18 '15 at 12:13

Raging Bull

18,593
13
50
55

@StefanMichev: We have to be superfast ;) – Raging Bull Aug 18 '15 at 12:16
Works, nice. This was fast. Thanks Bull:) – Kaptah Aug 18 '15 at 12:17
@Kaptah: See the demo added to the answer. Feel free to accept the answer if you are satisfied. – Raging Bull Aug 18 '15 at 12:22
May be we should use `COUNT(1)` instead of `COUNT(*)`. Anyway, great answer. – Kartic Aug 18 '15 at 12:24
@Kartic: Thanks. BTW, `COUNT(1)` and `COUNT(*)` won't make any difference here. See [**this post**](http://stackoverflow.com/questions/1221559/count-vs-count1) – Raging Bull Aug 18 '15 at 12:27

score 2 · Answer 2 · answered Aug 18 '15 at 12:14

2

Something like this

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

answered Aug 18 '15 at 12:14

Stefan Michev

4,795
3
35
30

Pரதீப் · Answer 3 · 2015-08-19T03:59:15.217

2

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

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

SQLFIDDLE DEMO

edited Aug 19 '15 at 03:59

answered Aug 18 '15 at 12:38

Pரதீப்

91,748
19
131
172

1

Nice that no additional join is added. [SQL Fiddle](http://sqlfiddle.com/#!6/bc02f/6) – ughai Aug 18 '15 at 13:38
@ughai - Thanks buddy – Pரதீப் Aug 18 '15 at 15:14

score 1 · Answer 4 · answered Aug 18 '15 at 12:40

Since you are using SQL server 2014 version, I'd recommend Windowed function used inside a self join update query type syntax.

update a
set a.cr= b.v
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 fiddle for demo http://sqlfiddle.com/#!6/bc02f/4

score 0 · Answer 5 · answered Aug 18 '15 at 12:16

0

;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

answered Aug 18 '15 at 12:16

M.Ali

67,945
13
101
127

Update column to the COUNT of rows for specific values in another column. SQL Server

5 Answers5

Linked