update one table column with count from other tabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update one table column with count from other tabl

Author

Topic

badinar
Starting Member

14 Posts

Posted - 2008-09-19 : 12:07:07

i have two tables

Category Table
---------------
Id
Name
NumberOfProducts

Products
---------
id
CategoryId
ProductName

I'm trying to update the NumberOfProducts Column of the category table
with this update statement but does not work.

update Category
set NumberOfProducts=(select count(*) from Products p where id = p.CategoryID)

some help please
Thanks,

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-19 : 12:30:45

What does not work? Update does nothing? Error message? Query deletes Product table? Server shutdown?

badinar
Starting Member

14 Posts

Posted - 2008-09-19 : 12:37:22

update just puts 0 on all rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 12:51:35

it should be this i guess

update c
set c.NumberOfProducts=p.ProductCount
FROM Category c
INNER JOIN
(select CategoryID,count(*) AS ProductCount 
from Products 
GROUP BY CategoryID)p
on c.id = p.CategoryID

badinar
Starting Member

14 Posts

Posted - 2008-09-19 : 13:08:59

That it visakh16!!. Just like that!!

thanks a lot!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:11:13

quote:
Originally posted by badinar

That it visakh16!!. Just like that!!

thanks a lot!!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources