Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: How to use temporarytable in such scenario, sql query DB2

    i want to perform query like this in which i select some fields like ITM_ID, EC_MC_ID, EC_RL_DT, DES_SEQNUM in inner select query and store tehm in temporary table. now i want to use values from that temp table to update some other table.
    Please guide me how can i perform such task. here i m getting error as temp table TEMP_PNEC is created afterwards but i m using it to update some table before its declaration

    UPDATE ERE2.OBJECT_DATA SET Q_DATREL = TEMP_PNEC.EC_RL_DT, Q_DESIGNSEQ = TEMP_PNEC.DES_SEQNUM
    WHERE EXISTS
    (SELECT *
    FROM (SELECT ITEM_OBJID, ITM_ID, EC_MC_ID, EC_RL_DT, DES_SEQNUM FROM "join of some tables" ) AS TEMP_PNEC
    WHERE
    OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID
    AND OBJECT_DATA.S_IDCNG = TEMP_PNEC.EC_MC_ID
    AND OBJECT_DATA.Q_PMSYNC='Y' AND OBJECT_DATA.S_DC='RD' AND OBJECT_DATA.Q_DATEPMSYNC < OBJECT_DATA.Q_LASTMOD
    )
    ;

    Thanks
    Prashant Aggarwal

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please use MERGE statement.

    http://www.dbforums.com/db2/1639978-...ary-table.html
    You can not use Common Table Expression in an UPDATE statement.

    Use EXISTS predicate with Nested Table Expression.
    Or MERGE statement, if it is neccesary to use column(s) of CTE in SET clause.

    .....

  3. #3
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by tonkuma
    Can you please illustrate using Merge statement?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are plenty of examples in the manual
    ---
    "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
  •