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

    Unanswered: Problem while doing Update using temporary table

    Can any one please tell me how can i make this query work, i tried doing some permutations on syntax but not working

    WITH TEMP_PNEC AS
    (SELECT ITEM_OBJID AS ITEM_OBJID, ITM_ID AS PN, EC_MC_ID AS EC FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC JOIN PRAGS.PART_LIST PART_LIST ON PART_LIST.S_ITEMI = AVNTMITEM.ITM_ID
    )
    UPDATE PRAGS.OBJECT_DATA SET Q_PMSYNC='Y' WHERE OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID AND OBJECT_DATA.S_IDCNG = TEMP_PNEC.EC

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

    Here is an example.
    Code:
    UPDATE PRAGS.OBJECT_DATA
       SET Q_PMSYNC='Y'
     WHERE EXISTS
           (SELECT *
              FROM (SELECT ITEM_OBJID AS ITEM_OBJID
                         , ITM_ID     AS PN
                         , EC_MC_ID   AS EC
                      FROM PRAGS.AVNTMITEM  AVNTMITEM
                      JOIN PRAGS.AVENTACI   AVENTACI
                       ON  AVNTMITEM.OID     = AVENTACI.OID_MITEM
                      JOIN PRAGS.AVNTEC     AVNTEC
                       ON  AVNTEC.OID        = AVENTACI.OID_EC
                      JOIN PRAGS.PART_LIST  PART_LIST
                       ON  PART_LIST.S_ITEMI = AVNTMITEM.ITM_ID
                   ) AS TEMP_PNEC
             WHERE
                   OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID
               AND OBJECT_DATA.S_IDCNG    = TEMP_PNEC.EC
           )
    ;

  3. #3
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by tonkuma
    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.

    Here is an example.
    Code:
    UPDATE PRAGS.OBJECT_DATA
       SET Q_PMSYNC='Y'
     WHERE EXISTS
           (SELECT *
              FROM (SELECT ITEM_OBJID AS ITEM_OBJID
                         , ITM_ID     AS PN
                         , EC_MC_ID   AS EC
                      FROM PRAGS.AVNTMITEM  AVNTMITEM
                      JOIN PRAGS.AVENTACI   AVENTACI
                       ON  AVNTMITEM.OID     = AVENTACI.OID_MITEM
                      JOIN PRAGS.AVNTEC     AVNTEC
                       ON  AVNTEC.OID        = AVENTACI.OID_EC
                      JOIN PRAGS.PART_LIST  PART_LIST
                       ON  PART_LIST.S_ITEMI = AVNTMITEM.ITM_ID
                   ) AS TEMP_PNEC
             WHERE
                   OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID
               AND OBJECT_DATA.S_IDCNG    = TEMP_PNEC.EC
           )
    ;

    Thanks , this worked.

  4. #4
    Join Date
    Jul 2009
    Posts
    58
    hi tonkuma,
    Can you tell me how to use the Merge statement with CTE.. one example will be helpful.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can you tell me how to use the Merge statement with CTE.. one example will be helpful.
    Please see this thread:
    http://www.dbforums.com/db2/1650265-...-together.html

    My opinions are:
    1) Can we use MERGE and WITH(common-table-expression) Together?
    No,
    Syntax of MERGE doesn't include CTE.

    2) If you are using DB2 Version 9.1 for z/OS,
    following statement may be possible.

    WITH cte AS (...)
    SELECT ...
    FROM FINAL TABLE(MERGE statement)

    3) It may be possible to UPDATE/INSERT in a statement with common-table-expressions, at least on DB2 8.2 or later for LUW.
    You can see an example in the thread.

Posting Permissions

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