Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

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.

like image 992
Kaptah Avatar asked Aug 18 '15 12:08

Kaptah


People also ask

How do you UPDATE a column based on another column in SQL?

UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.

How do I get counts of different values in the same column in SQL?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.

How do you UPDATE multiple columns in SQL with different conditions?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.


2 Answers

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

like image 119
Raging Bull Avatar answered Sep 28 '22 06:09

Raging Bull


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
like image 24
Stefan Michev Avatar answered Sep 28 '22 06:09

Stefan Michev