DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: problem in update statement

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    problem in update statement

    Hi ,

    i have two tables grp and grp_bkp ,

    I would like to update grp table particular column for a particular selection of rows from grp_bkp .

    but when i try to update table is getting locked .

    please let me know if i am missing anything .

    update grp a
    set cdng_value = ( select cdng_value from grp_bkp b
    where a.grp_id = b.grp_id
    and a.version_id = 12517
    and b.version = 12517 ) ;

    Grp_id is primary column .

    please let me know what i am missing .

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    you should add a exists condition else all the rows will be updated with null for non matching conditions

    Better use Merge Statement.. Its much faster..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: problem in update statement

    Originally posted by prakashs43
    Hi ,

    i have two tables grp and grp_bkp ,

    I would like to update grp table particular column for a particular selection of rows from grp_bkp .

    but when i try to update table is getting locked .

    please let me know if i am missing anything .

    update grp a
    set cdng_value = ( select cdng_value from grp_bkp b
    where a.grp_id = b.grp_id
    and a.version_id = 12517
    and b.version = 12517 ) ;

    Grp_id is primary column .

    please let me know what i am missing .
    You're updating the whole table. You need a WHERE clause for your UPDATE statement.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Prakash,

    Can you give more inputs,

    1. Your query hangs or come out with "some records updated"

    2. Is some relation between those tables. Any Foreigh keys ??



    Parsonal Query: Are you S.Prakash (SGU MCA 95) ?
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Dilippatel
    Parsonal Query: Are you S.Prakash (SGU MCA 95) ?
    look his profile.. does his b'day sounds so ..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi ,

    My Query is hanging , it seems it is doing something internally but taking very long time .
    so far not even a single record ha supdated .
    i have 10,000 records to update .
    this table has 100,000 records and backup table has 300,000 records

    grp table has several foreign keys .
    and 1 primary key on grp_id column

  7. #7
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    You are missing where clause with update statement. It should reduce the no records to update, and so to execution time.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    I tried like this but still this is taking lot of time ....

    update grp a
    set cdng_value = ( select cdng_value from grp_bkp b
    where a.grp_id = b.grp_id
    and a.version_id = b.version_id
    and b.version = 12517 )
    where a.version_id = 12517 ;

    explain plan for above statement is

    UPDATE STATEMENT Optimizer=CHOOSE (Cost=52 Card=2190 Bytes=26280)
    UPDATE OF GRP
    TABLE ACCESS (BY INDEX ROWID) OF GRP (Cost=52 Card=2190 Bytes=26280)
    INDEX (RANGE SCAN) OF XIF368GRP (NON-UNIQUE) (Cost=9 Card=2190)
    FILTER
    TABLE ACCESS (FULL) OF GRP_070904 (Cost=445 Card=32 Bytes=1216)


    COULD YOU PLEASE TELL HOW TO USE EXIST CLAUSE ...
    Last edited by prakashs43; 07-12-2004 at 11:21 AM.

  9. #9
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    I am trying using exists clause .
    is there any problem , even this query it is taking time ..
    no error message ..

    UPDATE grp a
    SET cdng_quest =
    (SELECT cdng_quest
    FROM grp_070904 b
    WHERE a.grp_id = b.grp_id
    AND a.version_id = b.version_id
    AND a.version_id = 27517)
    WHERE EXISTS (SELECT 'X'
    FROM grp_070904 b
    WHERE a.grp_id = b.grp_id
    AND a.version_id = b.version_id
    AND a.version_id = 27517 )
    and a.version_id = 27517 ;

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why would there be an error message? If I told you my car was not fast enough what would be the solution? You need to read http://www.dbasupport.com/forums/sho...threadid=36219 before blindly posting questions that we have no hope in answering.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width