Update Table from 2 Others With Count

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 Table from 2 Others With Count

Author

Topic

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 06:08:14

Hi, I have searched for some time this morning but so far unable to find an answer for my problem, but I am sure it my inexperience that causes this.

I have 3 tables, TBusiness, TBusinessNumber, TBusinessType

TBusinessType contains 2 columns, a unique BusinessTypeID and a BusinessCategoryID

TBusinessNumber contains 3 columns, a BusinessTypeID, BusinessCategoryID, and a NumberOfBusiness

TBusiness contains a list of all businesses in a town with many columns, but amongst them a BusinessTypeID.

I wish to update table TBusinessNumber with the count of unique BusinessTypeID in table TBusiness, and with the corresponding BusinessCategoryID from table TBusinessType

I have constructed a query to select the count of unique BusinessTypeID but that is as far as I get:

Select DISTINCT BusinessTypeID, Count(BusinessTypeID) From TBusiness Group By BusinessTypeID

This gives me a list of the unique BusinessTypesID and the count of how many time each appears (this is the value NumberOfBusiness I wish to insert into TBusinessNumber). I need to lookup each BusinessTypeID in table TBusinessType to find the corresponding BusinessCategoryID as well.

Help, please

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-07 : 06:35:08

update t
set NumberOfBusiness = s.cnt
from TBusinessNumber t
inner join (Select DISTINCT BusinessTypeID, Count(BusinessTypeID)as cnt From TBusiness Group By BusinessTypeID) s on s.BusinessTypeID = t.BusinessTypeID

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-07 : 07:19:47

[code]UPDATE TBusinessNumber
SET NumberOfBusiness =
COALESCE
(
(
SELECT COUNT(*)
FROM TBusiness B
WHERE B.BusinessTypeId = TBusinessNumber.BusinessTypeId
)
,0
)
,BusinessCategoryId =
(
SELECT T.BusinessCategoryId
FROM TBusinessType T
WHERE T.BusinessTypeId = TBusinessNumber.BusinessTypeId
)[/code]

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 07:22:37

@bklr - Many thanks for the quick reply. I think I can see where it is going, but it does not appear to look up the BusinessCategoryID from the table TBusinessType?

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 07:34:17

@ilfor, Thanks, think this is generally what I wanted but I have not explained one part of the requirement correctly. Before this query TBusinessNumber is empty. I want to populate it with each of the unique BusinessTypeID is used in table TBusiness. Your query returns zero, I think due to

WHERE B.BusinessTypeId = TBusinessNumber.BusinessTypeId

giving no matches.

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 07:49:48

Even more confused. If I run the query

Select DISTINCT BusinessTypeID From TBusiness Group By BusinessTypeID

I get a list of all the different BusinessTypeID used.

If I then run the query

UPDATE TBusinessNumber
SET BusinessTypeID =(
Select DISTINCT businesstypeID From tbl_business Group By BusinessTypeID
)

It replies with (0) rows affected?

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 08:28:54

OK, I have now found a query that gives me the 3 columns of results I want from the 2 tables, but how to update the 3rd table with them?

The query is:

Select DISTINCT TBusiness.BusinessTypeID , count(TBusiness.BusinessTypeID), TBusinessType.BusinessCategoryID
from TBusiness JOIN TBusinessType
ON (TBusiness.BusinessTypeID = TBusinessType.BusinessTypeID)
Group By TBusiness.BusinessTypeID, TBusinessType.BusinessCategoryID

geomal
Starting Member

6 Posts

Posted - 2009-05-07 : 08:58:07

All sorted now, thanks for the help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources