Query to update table

  • 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

  • Write a UDF to fetch the count for each CompanyID and call it from your select query.

    Thanks

     

    Prasad Bhogadi
    www.inforaise.com

  • 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

  • Hi,

    can u send me that code.

    Thanks,

    Ankur

  • 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 */

  • 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

  • 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

  • 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