2011. 5. 25. 14:14

[Oracle] JOIN UPDATE

SAMPLE

UPDATE /*+ BYPASS_UJVC */
(
  SELECT A.COLNM RA
             , B.COLNM RB 
     FROM A, B
   WHERE A.KEY= B.KEY
) SET RA = RB;

 

 

CASE 1

UPDATE  /*+ BYPASS_UJVC */

  SELECT
  B.BIZREGNO,  B.POINT,  A.MPOINT 
     FROM  ISS_CUST_REM_POINT B
             ,  ( SELECT  BIZREGNO  ,SUM(POINT) AS MPOINT
                      FROM  ISS_POINT_USE_DET
                    WHERE  USE_TP_CD = 'E01'
                        AND  TRAD_DE IN ('20110303','20110317')
                        AND REG_ID = 'admin11'
                    GROUP  BY  BIZREGNO
                )  A 
   WHERE  B.BIZREGNO=A.BIZREGNO
)
SET    POINT = POINT+MPOINT; 



CASE 2

  UPDATE  /*+ BYPASS_UJVC */                    
  (                                            
  SELECT  A.IBKAMT,  A.BADATE,  A.YONGCD,  B.IMONEY 
     FROM  MULCAL A,                             
            (  SELECT  Y.BADATE,  Y.YONGCD,  Y.IMONEY 
                   FROM  MULIBK X                     
                     JOIN  MULIBL Y                     
                       ON  Y.IBKNUM  =  X.IBKNUM          
                 WHERE  X.IBKNUM  =  '201105250001'            
            ) B                                   
   WHERE  A.BADATE  =  B.BADATE                   
       AND  A.YONGCD  =  B.YONGCD                   
  )                                            
  SET  IBKAMT  =  IBKAMT  -  IMONEY                 

 

'Database / Sql' 카테고리의 다른 글

[Oracle] BIN$... 으로 시작하는 테이블  (0) 2011.08.31
문자열 결합 그룹핑  (0) 2011.08.30
[Oracle] JOIN UPDATE (bypass_ujvc 힌트)  (0) 2011.05.25
[MSSQL] WHERE 절에 CASE문 추가  (0) 2011.03.21
[MSSQL] 날짜 함수  (0) 2010.11.30