Question on Bulk Update
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Question on Bulk Update

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Question on Bulk Update

    Hi,

    We have a table with 200 Columns and 200,000 rows.

    The Table looks something like this

    table_a

    col1
    col2
    col3

    I am trying to update col3 with (col1-col2) value for all the 200,000 rows.

    whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what´s wrong using a single update for a 200000 rows table....?

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    You can also write a procedure to update and commit after 10000 rows, just in case your rollback segment is small and you can not increase the size of it, but I would write one statement and update it after business hours.

    Just a thought

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Its not running oout of rollback segment. The problem is that it take around 25 minutes per update statement and i need to do 50 similar updates thats a total of 20 Hrs approx.

    I am sure that there must be a better and faster way to do a similar thing.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    probably yea, create a copy, truncate the table and do insert /*+ APPEND */ select

    but of course if you have 50 updates then you have to create like 50 temporary tables and truncate 50 times, and write 50 insert select where select you need all 200 columns

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

    Re: Question on Bulk Update

    Originally posted by ronnie
    whats the most efficient way to do this. please suggest. I need to do a similar thing for atleast 50 columns.
    50 columns of the same table? How about:
    Code:
    update table_a a
       set a.col3 = a.col1 + a.col2,
           a.col4 = a.col3 + a.col7,
           a.col34 = a.col69 + a.col54;
    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."

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    can you not do multiple updates at once, rather than waiting for one to finish before starting another?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Just create a PL/SQL procedure.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by irehman
    Just create a PL/SQL procedure.
    Uh oh, now you've said it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by irehman
    Just create a PL/SQL procedure.

    Not unless you have to!!!!

    But then, that's still better than manually doing 50 separate updates.

    As marist89 pointed out, what's the difficulty in updating all the columns at once with a single statement?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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