Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

LittleNick

Technical User
Jun 26, 2009
55
US
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.
 
Code:
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.
 
Try:

Code:
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
 
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
 
Imex, Your version works well also. Thank You!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top