CODEWITHSUNDEEP
sqlsql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60811

sql server 2008 query with if clause or case?

here is my data:

liza
liza
liza
alex
alex
alex
liza

i would like to update this table with data for every occurrence of every name like this:

liza    4
liza    4
liza    4
alex    3
alex    3
alex    3
liza    4

so far i have:

update table set column2=count(name) group by name

thanks so much for your help

Upvotes: 1

Views: 169

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

update T1 set
  Column2 = T2.C
from YourTable as T1
  inner join (
                select count(*) as C, Name
                from YourTable
                group by Name
             ) as T2  
    on T1.Name = T2.Name

Sample with a table variable

declare @YourTable table 
(
  Name varchar(10),
  Column2 int
)

insert into @YourTable (Name) values
('liza'),('liza'),('liza'),('alex'),('alex'),('alex'),('liza')

update T1 set
  Column2 = T2.C
from @YourTable as T1
  inner join (
                select count(*) as C, Name
                from @YourTable
                group by Name
             ) as T2  
    on T1.Name = T2.Name

Try it here. https://data.stackexchange.com/stackoverflow/q/102392/update-with-count

Here is another way using a CTE and count(*) over().

declare @YourTable table 
(
  Name varchar(10),
  Column2 int
)

insert into @YourTable (Name) values
('liza'),('liza'),('liza'),('alex'),('alex'),('alex'),('liza')

;with cte as
(
  select Column2,
         count(*) over(partition by Name) as C
  from @YourTable        
)
update cte set
  Column2 = C

And this query you can try here. https://data.stackexchange.com/stackoverflow/q/102394/update-with-count-2

Upvotes: 5

Related Questions