DB2 Update Statement using sub-query – Use MERGE

Thanks to Serge Rielau for insight in DB2 MERGE.

For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution.

Oracle UPDATE

UPDATE 
(
      SELECT clm.c2 wc1,
             m.c2 wc2
      FROM   schema1.table1 m,  
             schema1.table2 clm
      WHERE  clm.c1 = m.c1
         and clm.date1 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.date2 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.flag = 'N'
) joined
SET joined.wc1 = joined.wc2;

DB2 Merge

MERGE INTO schema1.table1 m
  USING  schema1.table2 clm
   ON  clm.c1 = m.c1
         and clm.date1 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.date2 between to_date('10012008','MMDDYYYY') and  to_date('12312008','MMDDYYYY')
         and m.flag = 'N'
WHEN MATCHED THEN UPDATE SET m.c2 = clm.c2;

SQL Server Update – 2 table issue

update t
 set book = r.book
 from #RecLookup r
	 JOIN TABLE_TOBE_UPDATED t
		 ON t.history_date = CONVERT ( char(8) , @CobDate, 112)
			 and t.rec_id = r.rec_id
 where (r.update_options & 0x1) = 0x1 
	AND ((@MinRecId = 0 and @MaxRecId = 0x7FFFFFFF) OR 
	     (t.rec_id >= CONVERT(varchar(12),@MinRecId) 
   		 and t.rec_id <= CONVERT(varchar(12),@MaxRecId)))

DB2 using MERGE

MERGE INTO TABLE_TOBE_UPDATED T
USING  SESSION.TT_RECLOOKUP R
  ON   T.HISTORY_DATE = V_COBDATE
     AND  T.REC_ID = R.REC_ID
     AND (BITAND(R.UPDATE_OPTIONS,1)) = 1 
     AND  ((V_MINRECID = 0 AND V_MAXRECID = 2147483647) OR 
           (T.REC_ID >= V_MINRECID AND T.REC_ID <= V_MAXRECID))
WHEN MATCHED THEN UPDATE SET
   T.BOOK = R.BOOK;

Or, another MERGE. The explain plan will tell you which one is better.

MERGE INTO 
(
  SELECT REC_ID, BOOK FROM TABLE_TOBE_UPDATED
  WHERE HISTORY_DATE = V_COBDATE
  AND ((V_MINRECID = 0 AND V_MAXRECID = 2147483647) OR  
        (REC_ID >= V_MINRECID AND REC_ID <= V_MAXRECID))

) AS T
USING 
(
   SELECT REC_ID, BOOK FROM SESSION.TT_RECLOOKUP
   WHERE (BITAND(UPDATE_OPTIONS,1)) = 1 
) AS R
ON T.REC_ID = R.REC_ID 
  WHEN MATCHED THEN UPDATE SET
     T.BOOK = R.BOOK;

SQL Server UPDATE returning more than one row for each matched row

Take this example of notorious UPDATE in SQL Server where more than one row is returned for each row matched in the table_tobe_updated. This is a feature of SQL Server you realize when porting to DB2. When you try to match this in DB2, you might get -811 error.

The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.. SQLCODE=-811, SQLSTATE=21000

SQL Server Update returning more than one row for each matched row

update   tmp                                                     
set                                                              
   trading_book_id = bk_Riskmap.book_id,                         
   trading_book_name = bk_Riskmap.book_name,                     
   party_book_name = bk_Riskmap.counterparty_book_name,          
   party_book_id = bk_Riskmap.counterparty_book_id,              
   cost_center = bk_Riskmap.cost_center,                         
   Risk_Reason = bk_Riskmap.risk_reason,                         
   mapping_type = (tmp.mapping_type | 0x2)                       
from  TABLE_TOBE_UPDATED tmp                                     
   JOIN (                                                        
      select book_name, edsi_id, book_hist_id = MIN(book_hist_id)
      from dbo.book_hist                                         
      where history_date = @CobDate                              
         and (options & 0x2) = 0x2            
      group by book_name, edsi_id                                
   ) UList                                                       
      ON tmp.book = UList.book_name                              
         and tmp.edsi_id = UList.edsi_id                         
   JOIN dbo.book_hist bk_Riskmap                                 
      ON bk_Riskmap.history_date = @CobDate                      
         and UList.book_hist_id = bk_Riskmap.book_hist_id        

DB2 UPDATE - USING MERGE

MERGE INTO TEMP.TABLE_TOBE_UPDATED TMP
USING
( 
   SELECT ULIST.BOOK_NAME, ULIST.EDSI_ID, MIN(ULIST.BOOK_HIST_ID) BOOK_HIST_ID    
     FROM BOOK_HIST UList                                           
     WHERE UList.HISTORY_DATE = '2009-03-31'                            
        AND BITAND(UList.OPTIONS,2) = 2                                             
     GROUP BY ULIST.BOOK_NAME, ULIST.EDSI_ID   
) AS U (BOOK_NAME, EDSI_ID, BOOK_HIST_ID)
ON TMP.BOOK = U.BOOK_NAME
  AND TMP.EDSI_ID = U.EDSI_ID
WHEN MATCHED THEN UPDATE SET                                    
   (TRADING_BOOK_ID,TRADING_BOOK_NAME,PARTY_BOOK_NAME,
    PARTY_BOOK_ID,COST_CENTER,RISK_REASON,MAPPING_TYPE) =  
(
SELECT BOOK_ID, BOOK_NAME, 
       COUNTERPARTY_BOOK_NAME,
       COUNTERPARTY_BOOK_ID, 
       COST_CENTER,
       RISK_REASON, 
       BITOR(TMP.MAPPING_TYPE, 2) 
FROM  BOOK_HIST BK_RISKMAP
WHERE  BK_RISKMAP.HISTORY_DATE = '2009-03-31'                       
AND U.BOOK_HIST_ID = BK_RISKMAP.BOOK_HIST_ID
)@

Rule of thumb: You should never have to use TARGET table in the USING clause.