Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2015
    Posts
    2

    Post Unanswered: How to UPDATE More Than 500 Records in One Call

    Hi All,

    I have following query which is created dynamically as -

    UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
    UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
    UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
    UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
    UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';

    Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.

    I want to increase performance of application.

    Which is the best way to do this ? Any other class like BulkCopy ? Please suggest solution.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Assuming that you are using a query like this to build this:

    Code:
    select 'UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '''||Set_Number||''' AND SEND_DATE = '''||char(Send_Date)||''';' from MyTable where ...
    Why not just do this:

    Code:
    update buildtable set BUILD_ID = '984137' where (set_number,dend_date) in (select Set_Number, from MyTable where ...)
    Why bother with the edit step?

    Andy

  3. #3
    Join Date
    Aug 2015
    Posts
    2
    Hi Andy,

    Thanks for reply.

    If you see the query "SET_NUMBER" & "SEND_DATE" has different values in each UPDATE query.

    Your Query -

    update buildtable set BUILD_ID = '984137' where (set_number,dend_date) in (select Set_Number, from MyTable where ...)

    In the where clause I have I have to write 600 condition .. right ?

    Is there any way for Update like DB2BulkCopy for insertion ?

  4. #4
    Join Date
    Aug 2015
    Posts
    11
    Try this logic

    OPEN cursor1;
    fetch cursor1 into param1 ,param2;

    WHILE sqlcode = 0 DO

    SET count = count + 1 ;

    update SCHEMA.MY_TABLE set my_column = (
    select some column from SCHEMA.MY_TABLE1 where condition_column = 'some value' fetch first row only )
    where my_column=param1;
    if cnt > 1000 then
    commit;
    set count = 0 ;
    end if ;
    fetch cursor1 into param1 ,param2;
    END WHILE;

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Sudarshan2788 View Post
    Hi Andy,

    Thanks for reply.

    If you see the query "SET_NUMBER" & "SEND_DATE" has different values in each UPDATE query.

    Your Query -

    update buildtable set BUILD_ID = '984137' where (set_number,dend_date) in (select Set_Number, from MyTable where ...)

    In the where clause I have I have to write 600 condition .. right ?

    Is there any way for Update like DB2BulkCopy for insertion ?
    Not necessarily (to writing 600 conditions). What query did you use to create the list of 600 update statements?

    Andy

Posting Permissions

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