How to use temporarytable in such scenario, sql query DB2

cprash.aggarwal · #1 (**permalink**) 03-26-09, 12:47

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

tonkuma · #2 (**permalink**) 03-26-09, 16:39

Please use MERGE statement.

Problem while doing Update using temporary table

Quote:

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.

.....