Post reply

Query to update table

  • February 27, 2007 at 8:48 am

    #172277

    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

    #692078

    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

    #692080

    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

    #692081

    Hi,

    can u send me that code.

    Thanks,

    Ankur

  • February 28, 2007 at 3:51 am

    #692253

    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

    #692258

    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

    #692264

    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

    #693030

    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