-- 테스트용 테이블 생성 --
CREATE TABLE test9
AS
SELECT '20230403' ymd, '고재원' un, 40297 a, 40307 b FROM dual
UNION ALL SELECT '20230403', '고재원', 40307, 40317 FROM dual
UNION ALL SELECT '20230404', '고재원', 40317, 40327 FROM dual
UNION ALL SELECT '20230404', '고재원', 40327, 40337 FROM dual
UNION ALL SELECT '20230405', '고재원', 40337, 40347 FROM dual
UNION ALL SELECT '20230405', '고재원', 40347, 40357 FROM dual
UNION ALL SELECT '20230406', '고재원', 40357, 40367 FROM dual
UNION ALL SELECT '20230406', '고재원', 40367, 40377 FROM dual
UNION ALL SELECT '20230407', '고재원', 40377, 40387 FROM dual
UNION ALL SELECT '20230407', '고재원', 40387, 40397 FROM dual
;

-- 입력용 테이블 생성 --
CREATE TABLE test9_insert
AS
SELECT * FROM test9
 WHERE 1=2
;

-- 추가 데이터 입력 --
INSERT INTO test9_insert VALUES('20230405', '고재원', 40357, 40369);
INSERT INTO test9_insert VALUES('20230405', '고재원', 40369, 40381);

SELECT * FROM test9_insert
;

-- 기존 테이블 갱신 및 추가 자료 입력 --
MERGE INTO test9 t
USING (SELECT un
            , MIN(a) a
            , MAX(b) - MIN(a) c
         FROM test9_insert
        GROUP BY un
       ) s
ON (t.un = s.un)
WHEN MATCHED THEN
    UPDATE
       SET t.a = t.a + s.c
         , t.b = t.b + s.c
     WHERE t.a >= s.a
;

INSERT INTO test9
SELECT * FROM test9_insert
;

-- 입력용 테이블 초기화 --
TRUNCATE TABLE test9_insert;

-- 최종 데이터 확인 --
SELECT *
  FROM test9
 ORDER BY un, a
;