Problem while doing Update using temporary table

cprash.aggarwal · #1 (**permalink**) 03-21-09, 02:54

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

tonkuma · #2 (**permalink**) 03-21-09, 04:02

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
       )
;

cprash.aggarwal · #3 (**permalink**) 03-21-09, 06:26

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.

mac4rfree · #4 (**permalink**) 11-17-09, 04:22

hi tonkuma,
Can you tell me how to use the Merge statement with CTE.. one example will be helpful.

tonkuma · #5 (**permalink**) 11-18-09, 02:48

Quote:

Can you tell me how to use the Merge statement with CTE.. one example will be helpful.

Please see this thread:
can we use Merge and With Together

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech