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.
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.
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.
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With