Update a field usin count of another field of the same table 1

[LittleNick]

Hi All,
I have a table like this

ID Item RcdCount
02 x
02 y
02 z
05 a
05 b
04 j


And I want to update it to look like this

ID Item RcdCount
02 x 3 (Because there are 3 records of ID 02)
02 y 3
02 z 3
05 a 2
05 b 2
04 j 1

Thanks for your helps.

 

[bborissov]

UPDATE YourTable Set RcdCount = Tbl1.Cnt
FROM YourTable
INNER JOIN (SELECT Id, COUNT(*) AS Cnt
                   FROM YourTable
            GROUP BY Id) Tbl1
      ON YourTable.Id = Tbl1.Id

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[imex]

Try:

with TableCount as
  (select ID, Count(*) as RcdCount from TableItens group by ID)

update TableItens 
set RcdCount = (select RcdCount from TableCount where TableCount.ID = TableItens.ID)

I hope this helps.


Casas a venda em Suzano

 

[LittleNick]

Thanks bborissov. That works!


Thanks Imex for your help. I have not tried your solution yet but I will and will let you know. Thanks

 

[LittleNick]

Imex, Your version works well also. Thank You!