1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

many select statments to update table

Discussion in 'SQL PL/SQL' started by pure_co, Jun 28, 2009.

  1. pure_co

    pure_co Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    hi all,

    i need to update ADSL_SPEED field in table (ADSL_TEST), to get this value i had to use many select statements. The problem is i don't get any result; the execution of the code seems hang up.
    Code (Text):

    update adsl_test at
         set at.adsl_speed = (select e.adsl_service
                             from ne.mv_equipment e, adsl_test at
                             where e.equipment_name = (select ec.gis_equipment_name
                                                       from equipment_conversion ec
                                                       where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                          from ccbs.pairs_status_view psv
                                                                          where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                               )
                                                     )
                            )
    Where at.tel_no in (select telno from ccbs.pairs_status_view);

     
    i tried to test the code by execute the select statments only but i get no row selected. this is the code
    Code (Text):

     select e.adsl_service, b.tel_no
                             from ne.mv_equipment e, adsl_test b
                             where e.equipment_name = (select ec.gis_equipment_name
                                                       from equipment_conversion ec
                                                       where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                          from ccbs.pairs_status_view psv
                                                                          where psv.cable_name_down is null and b.tel_no = 064616351
                                                                           )
                                                                                                 );
     
    but when i removed the using of ADSL_TEST it works fine ?!
    what is i'm doing wrong??
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is becoz.. you used adsl_test two times. (one in Main query and another in Sub query), But in you refrerenced only Main query adsl_test. ie, subquery adsl_test is not referenced anywhere .. so it lead to duplication of data. Thats why it was hanging.

    Still I suggest you for EXISTS clause fro better perfomance..
     
  3. pure_co

    pure_co Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thank you for your response, i make few update but still have the same result. see my code now...
    Code (Text):

    update adsl_test at
         set at.adsl_speed = (select e.adsl_service
                             from ne.mv_equipment e, adsl_test at
                             where e.equipment_name = (select ec.gis_equipment_name
                                                       from equipment_conversion ec
                                                       where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                          from ccbs.pairs_status_view psv
                                                                          where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                               )
                                                     )
                            )
    Where EXISTS (select at.tel_no ,psv.telno
                         from ccbs.pairs_status_view psv,adsl_test  at
                         where at.tel_no =psv.telno );

     
    and see the discription of my tables :
    1-ADSL_TEST;
    TEL_NO,ADSL_SPEED

    2-ccbs.pairs_status_view;
    DOWN_EQPT_ID,TELNO, CABLE_NAME_DOWN

    3-ccbs.equipment_conversion;
    GIS_EQUIPMENT_NAME,CCBS_EQPT_ID.

    4- ne.equipment;
    ADSL_SERVICE,EQUIPMENT_NAME
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why do you need to refer adsl_test at in the following places ?

    Code (Text):

    update adsl_test at
         set at.adsl_speed = (select e.adsl_service
                             from ne.mv_equipment e, [B][COLOR=red]adsl_test at[/COLOR][/B]
                             where e.equipment_name = (select ec.gis_equipment_name
                                                       from equipment_conversion ec
                                                       where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                          from ccbs.pairs_status_view psv
                                                                          where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                               )
                                                     )
                            )
    Where EXISTS (select at.tel_no ,psv.telno
                         from ccbs.pairs_status_view psv,[B][COLOR=red]adsl_test at[/COLOR][/B]
                         where at.tel_no =psv.telno );

     
    I was asking to put the EXISTS clause inside the subsquery also.

    for example ,

    WHERE EXISTS in main query could be changed to ,

    Code (SQL):

    WHERE EXISTS (SELECT NULL
                         FROM ccbs.pairs_status_view psv
                         WHERE at.tel_no =psv.telno )
     
    NB: It doesn't matter what Select query return in EXISTS clause.

    Do the same for Subquery and let us know the feedback ..
     
  5. pure_co

    pure_co Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    i tried to know how i can use exisit clause , still don't know how i can add it to subsquery. can you help me with this please?
    i referenced adsl_test at table twice by mistake, this is what i have done ...
    Code (Text):

    update adsl_test at
         set at.adsl_speed = (select e.adsl_service
                             from ne.mv_equipment e
                             where e.equipment_name = (select ec.gis_equipment_name
                                                       from equipment_conversion ec
                                                       where ec.ccbs_eqpt_id in (select distinct psv.down_eqpt_id
                                                                          from ccbs.pairs_status_view psv
                                                                          where at.tel_no in psv.telno and psv.cable_name_down is null
                                                                               )
                                                     )
                            )
    Where EXISTS (select null from ccbs.pairs_status_view psv where at.tel_no =psv.telno );

     
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This could be changed something like below .. . (Not Tested)

    Code (SQL):

    UPDATE adsl_test at
       SET at.adsl_speed =
              (SELECT e.adsl_service
                 FROM ne.mv_equipment eq
                WHERE eq.tel_no = at.tel_no
                  AND EXISTS (SELECT NULL
                                FROM equipment_conversion ec
                               WHERE ec.gis_equipment_name =eq.equipment_name
                                 AND EXISTS (SELECT NULL
                                               FROM ccbs.pairs_status_view psv
                                              WHERE psv.down_eqpt_id = ec.ccbs_eqpt_id
                                                AND psv.telno = at.tel_no
                                                AND psv.cable_name_down IS NULL)))                    
     WHERE EXISTS (SELECT NULL
                     FROM ccbs.pairs_status_view psv
                    WHERE AT.tel_no = psv.telno);
     
    There could chances of errors as we dont know how the data is sitting in thses tables.. ..
     
  7. pure_co

    pure_co Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    i removed (WHERE eq.tel_no = at.tel_no) as eq.tel_no not found in ne.mv-equipment table.
    Code (Text):

    UPDATE adsl_test at
       SET at.adsl_speed =
              (SELECT eq.adsl_service
                 FROM ne.mv_equipment eq
                            WHERE EXISTS (SELECT NULL
                                           FROM equipment_conversion ec
                                          WHERE ec.gis_equipment_name =eq.equipment_name
                                         and EXISTS (
                                                    SELECT NULL
                                                      FROM ccbs.pairs_status_view psv
                                                     WHERE psv.down_eqpt_id = ec.ccbs_eqpt_id
                                                       AND psv.telno = at.tel_no
                                                       AND psv.cable_name_down IS NULL)))
     WHERE EXISTS (SELECT NULL
                     FROM ccbs.pairs_status_view psv
                    WHERE at.tel_no = psv.telno);
     
    i still have the same problem of hanging !!
    by the way some select statment might not return any values, and some might have more than one by mistake !!
    is this what cause this ?
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Query could be hanging because of several reasons like improper use or absence of index, Huge data , slow running server etc.

    What you can do now is,

    1. To check if all relavant columns are properly indexed
    2. Get a tel_no that gives the o/p for the subquery and check with the main query
    3. Ask DB for DB/Server perfomance . .
    4. Check if the data is consistent.