Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Multiple Update for DB2

    I have used MS SQL and only recently moved to DB2. Multiple updates are no problem in MS SQL and are a mission in DB2.

    We have two similar tables with almost the same content. The second one of these became redundant and needs to be dropped. Unfortunately the table has more columns than the other and needs to be carried over. I have created the columns in table A and need to copy the data from B to A into the newly created columns where they matchup.

    I have tried:

    UPDATE TABLEA
    SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
    WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);
    AND

    MERGE INTO MAPP_LEDGER_DOUBLE_ENTRY_TABLE tDoubleLedger
    USING (SELECT DISTINCT REQUEST_ID, ENTETY_ID, PRODUCT_ID FROM MAPP_TRANSACTION) tTransaction
    ON tTransaction.REQUEST_ID = tDoubleLedger.REQUEST_ID
    AND tTransaction.ENTETY_ID = tDoubleLedger.ENTITY_ID
    WHEN MATCHED THEN
    UPDATE SET
    PRODUCT_ID = tTransaction.PRODUCT_ID;
    Both these take too long and we can not have that much down time. I have not tried cursors yet, because they normaly perform much slower.

    Please describe the best way of doing Multiple Updates in DB2, transfering data from one table to the other.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    My guess would be that you either don't have appropriate indexes for your statements or the statistics are not up to date.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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