A 라는 테이블이 게시글 마스터테이블이고, B가 게시글에 대한 옵션정보인데
B에 있는 컬럼을 가지고 A의 값을 업데이트 중에 있습니다
select
ADD_MONTHS(update_date, (select TO_NUMBER(CLOSE_TERM) from wo00066.tbo2020 where a1.bulletincode = bulletincode
and a1.threadno = threadno)*12) - 1 closedate_new
, update_date
, closedate
, a1.*
from wo00066.tbo2010 a1
where not exists (select 1 from WO00066.TBO2020 a2 where
a1.bulletincode = a2.bulletincode
and a1.threadno = a2.threadno
and close_term not in ('0','9')
and rownum = 1)
and closedate > sysdate
;
여기서 나오는 closedate_new 값을 TBO2010에 CLOSEDATE 값으로 업데이트 해주어야 하는데
TBO2020에 NOT IN 조건 때문에
TBO2020에서 누락된 건에 대해서 TBO2010에 빈 값이 업데이트 쳐지는 문제가 발생합니다
어떻게 조인을 걸어서 업데이트를 해야하나요ㅠ
update tbo2010 b1 set closedate = (
select closedate_new
from
(
select ADD_MONTHS(update_date, (select TO_NUMBER(CLOSE_TERM) from wo00066.tbo2020 where a1.bulletincode = bulletincode
and a1.threadno = threadno)*12) - 1 closedate_new
, update_date
, closedate
, a1.bulletincode
, a1.threadno
from wo00066.tbo2010 a1
where not exists (select 1 from WO00066.TBO2020 a2 where
a1.bulletincode = a2.bulletincode
and a1.threadno = a2.threadno
and a2.close_term not in ('0','9')
and rownum = 1)
and closedate > sysdate) t where t.bulletincode = b1.bulletincode and t.threadno = b1.threadno)
where (bulletincode, threadno) in (select bulletincode, threadno from tbo2020 where close_term not in ('0','9'))
ㅇㅣ렇게 하였는데도 빈값이 발생합니다ㅠㅠㅠ
-- 0. 모든 조건절을 일치시켜 주세요. -- -- 1. 확인용 -- SELECT update_date , closedate -- , (SELECT ADD_MONTHS(a.update_date, b.close_term) * 12 - 1 -- 오류 , (SELECT ADD_MONTHS(a.update_date, b.close_term * 12) - 1 -- 수정 FROM tbo2020 b WHERE b.bulletincode = a.bulletincode AND b.threadno = a.threadno AND b.close_term NOT IN ('0','9') ) closedate_new FROM tbo2010 a WHERE EXISTS (SELECT 0 FROM tbo2020 b WHERE b.bulletincode = a.bulletincode AND b.threadno = a.threadno AND b.close_term NOT IN ('0','9') ) ; -- 2. 갱신용 -- UPDATE tbo2010 a SET closedate -- = (SELECT ADD_MONTHS(a.update_date, b.close_term) * 12 - 1 -- 오류 = (SELECT ADD_MONTHS(a.update_date, b.close_term * 12) - 1 -- 수정 FROM tbo2020 b WHERE b.bulletincode = a.bulletincode AND b.threadno = a.threadno AND b.close_term NOT IN ('0','9') ) WHERE EXISTS (SELECT 0 FROM tbo2020 b WHERE b.bulletincode = a.bulletincode AND b.threadno = a.threadno AND b.close_term NOT IN ('0','9') ) ;
closedate는 date 입니다 ㅠㅠ
SELECT update_date
, closedate
, (SELECT ADD_MONTHS(a.update_date, to_number(b.close_term)) * 12 - 1
FROM tbo2020 b
WHERE b.bulletincode = a.bulletincode
AND b.threadno = a.threadno
AND b.close_term NOT IN ('0','9')
) closedate_new
FROM tbo2010 a
WHERE EXISTS (SELECT 0
FROM tbo2020 b
WHERE b.bulletincode = a.bulletincode
AND b.threadno = a.threadno
AND b.close_term NOT IN ('0','9')
)
;
이렇게 to_number로 감쌋는데도 ㅠㅠ