안녕하세요 ^^ 신입개발자 입니다.

다름이 아니오라 같은 테이블에 insert랑 update를 비교를 해서 하고 싶은데

검색을 해서 찾아봐도 잘 몰라서 질문 드립니다 ㅠㅠ

MERGE INTO tb_plan_oi t
  USING (SELECT x.accounting_yyyymm
               ,x.division_code
               ,MAX (X.region_code) AS region_code
               ,MAX (X.nation)      AS nation
               ,MAX (X.affiliate_branch_code) AS affiliate_branch_code
               ,MAX (REPLACE(x.sales_plan_qty,',','')) AS sales_plan_qty
               ,MAX (REPLACE(X.sales_plan_usd,',','')) AS sales_plan_usd
               ,MAX (REPLACE(X.oi_plan_usd,',',''))    AS oi_plan_usd
               ,MAX (X.last_update_by) last_update_by
           FROM (SELECT t.accounting_yyyymm AS accounting_yyyymm
                       ,t.division_code         AS division_code
                       ,t.region_code           AS region_code
                       ,t.nation                AS nation
                       ,t.affiliate_branch_code AS affiliate_branch_code
                       ,t.sales_plan_qty        AS sales_plan_qty
                       ,t.sales_plan_usd        AS sales_plan_usd
                       ,t.oi_plan_usd           AS oi_plan_usd
                       ,t.last_update_by        AS last_update_by
                   FROM tb_plan_oi t
                   ,(SELECT substr(t.accounting_yyyymm,1,4) AS yyyy
                       FROM tb_plan_oi t) y
                      WHERE T.division_code     = nvl(?, T.division_code)
                        AND y.yyyy = ?
                   ORDER BY t.accounting_yyyymm
                   ) x
 GROUP BY x.division_code
        ,x.accounting_yyyymm) rt
        ON (t.accounting_yyyymm = rt.accounting_yyyymm)
 WHEN MATCHED THEN
        UPDATE SET  oi_plan_usd                   = ?
            , sales_plan_usd                = ?
            , sales_plan_qty                = ?
               , last_update_by                = ?   
               , last_update_date = SYSDATE       
           WHERE division_code                 = ?
             AND accounting_yyyymm             = ?
 WHEN NOT MATCHED THEN
         INSERT (x.division_code, x.region_code, x.affiliate_branch_code, x.oi_plan_usd, x.sales_plan_usd, x.sales_plan_qty, x.accounting_yyyymm, SYSDATE, x.create_by, SYSDATE , x.last_update_by)
         VALUES (?, ?, ?, ?, ?, ?, ?, ? , SYSDATE, ? , SYSDATE , ?)

질문의 요지 ... 일단 같은 테이블에 insert랑 update하는 거구요

tb_plan_oi 테이블에 accounting_yyyymm 컬럼이 있는데 지금 5개 들어가 있습니다

"200901","200902","200903","200904","200905" 들어가있구요 1월-5월로 나뉘어져 있습니다.

1-5월 사이에 데이타를 수정하면 update를 하고 싶구요

200906이 없으면 insert를 하고 싶은데 on condition조건을 어떻게 하면 될꺼 같은데

도저히 모르겠습니다.. 가르침을 내려주세요 ㅠㅠ