오라클 update 복구 / 서브쿼리

참고한내용은 다음과같다.

------------------------------------------------------------------------------------------------------------------------

update [테이블명] A set [컬럼명] = (

select

[컬럼명]

from

[테이블명] as of TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)

where

NO = A.NO

)

------------------------------------------------------------------------------------------------------------------------

사용예 )TB_SLSSONMST 테이블의 WDAY_SESN_CD 필드값을 원복해야하는상황

update TB_SLSSONMST A SET WDAY_SESN_CD = (

select

WDAY_SESN_CD

from

TB_SLSSONMST as of TIMESTAMP(SYSTIMESTAMP-INTERVAL '40' MINUTE) -- (40분전 (TB_SLSSONMST )데이터 사용)

where

SESN_DATE BETWEEN TO_DATE('20150101','YYYYMMDD') AND TO_DATE('20160409', 'YYYYMMDD')

AND SESN_TYPE_CD = 'GF001'

AND OUTLET_CD = '441000'

AND SESN_DATE = A.SESN_DATE -- 서브쿼리 결과 (다중행을 모두 업데이트 하기위해 조건 적용)

)

,

INPUT_DTTM = sysdate

where

SESN_DATE BETWEEN TO_DATE('20150101','YYYYMMDD') AND TO_DATE('20160409', 'YYYYMMDD')

AND SESN_TYPE_CD = 'GF001'

AND OUTLET_CD = '441000'

https://itstory07.tistory.com/1284

https://itstory07.tistory.com/1284

------------------------------------------------------------------------------------------------------------------------

commit~!!