February 27, 2007 at 8:48 am
Hi,
I have 2 tables table1 and table2
table1 ---
CompanyId CompanyName NoOfBranches
1 G-Motors 0
2 Honda 0
3 Suzuki 0
Table2----------
CompanyId Branch
1 Delhi
1 Bombay
1 Chennai
2 London
2 Newyork
3 Melborne
3 Washington
3 Durban
I want to write a query that will count the no of branches of a company and update table1 with the no of branches.
I have done it using cursors but as everyone knows cursors are ver expensive in terms of performance.
So I need other option that give better performance because size of my table is big (around 50,000 rows)
Any help will be appriciated.
Thanks,
Ankur
February 27, 2007 at 8:51 am
Write a UDF to fetch the count for each CompanyID and call it from your select query.
Thanks
Prasad Bhogadi
www.inforaise.com
February 27, 2007 at 8:53 am
Ankur
Is this a homework question? What have you come up with so far, apart from the cursor method? Here's a hint: try writing it as a SELECT statement before you try doing the update, to make sure you're getting the correct results. Use a LEFT OUTER JOIN on the CompanyID column.
John
February 27, 2007 at 8:54 am
Hi,
can u send me that code.
Thanks,
Ankur
February 28, 2007 at 3:51 am
Hi Ankur ,
Use this Query!! I thought it might help you..
update a set noofbranches=Branch
from Company a join
(select * from (
select companyid,count(*) Branch
from Company_Branch
group by companyid)a)b
on a.companyid=b.companyid
Regards,
Amit G.
/* Simplest Way Out Just Find a Way */
February 28, 2007 at 4:14 am
You're spoiling him, Amit! Note that your query doesn't account for the possibility that there may be companies with no branches defined in Table2. You can solve this by using a LEFT JOIN and testing for NULLs in b.CompanyID.
John
February 28, 2007 at 4:28 am
Hi John, thanks for setting the things straight. It is as simple as what you said
SELECT
a.CompanyID,
a
.CompanyName,
COUNT(b.CompanyID)AS NoofBranches
FROM
Company a
LEFT
OUTER JOIN
CompanyDtls b
ON a.CompanyID = b.CompanyID
GROUP
BY
a
.CompanyID,
a
.CompanyName
Should do it. You can update the counts using the update statement if you really want to have a column in your master table. Replace with appropriate Table names.
Prasad Bhogadi
www.inforaise.com
March 5, 2007 at 12:07 am
this will definitely update the no. of branches, it will also update those rows having no branches.
Update tab1 set NoofBranch = ( Select Count(*) from tab2 where id = tab1.id )
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply