[ Oracle ] table to table로 다중 업데이트 방법

 

테이블의 데이터를 select하여 바로 update 처리하는 방법

 

 

-- 조건 구분 및 중복데이터 제거 등 처리할때 사용 

 

UPDATE world MM SET

( 

   name, auth, code, cont, ndate, ntype ) 

) = ( 

  SELECT 

     BB.n_name AS name

     , BB.n_auth AS auth

     , 'A001' AS code

     , BB.n_cont AS cont

     , TO_CHAR(SYSDATE, 'YYYYMMDDHHMMSS')  AS ndate

     , CASE WHEN SUBSTR ( BB.n_type, 0, 8) > '20180130' THEN 'A' ELSE 'D' END AS ntype

  FROM world AA

  , korea BB

  WHERE AA.scode = '10'

  AND AA.sqnc = BB.n_sqnc

  AND BB.ndate > '20170915990000'

  AND MM.sqnc = AA.sqnc

  AND MM.auth = BB.n_auth

)

WHERE EXISTS (

    SELECT BB.n_sqnc AS sqnc

    FROM world AA

    , korea BB

    WHERE AA.scode = '10'

    AND AA.sqnc = BB.n_sqnc

    AND BB.ndate > '20170915990000'

    AND MM.sqnc = AA.sqnc

    AND MM.auth = BB.n_auth

)

;

-- 간단한 조건일때 

 

UPDATE world MM SET

( 

   name, auth, code, cont, ndate, ntype ) 

) = ( 

  SELECT 

     BB.n_name AS name

     , BB.n_auth AS auth

     , 'A001' AS code

     , BB.n_cont AS cont

     , TO_CHAR(SYSDATE, 'YYYYMMDDHHMMSS')  AS ndate

     , CASE WHEN SUBSTR ( BB.n_type, 0, 8) > '20180130' THEN 'A' ELSE 'D' END AS ntype

  FROM korea BB

  WHERE MM.sqnc = BB.n_sqnc

  AND MM.auth = BB.n_auth

)

WHERE MM.sqnc IS NOT NULL

;