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:
Quote:
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
Quote:
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.
dBforums
>
Multiple Update for DB2
09-22-09, 06:56
Linear Mode
