-- Source table --
CREATE TABLE test1
AS
SELECT 721 header_id, 'S01' mug02, -1 parent_id, 'Pilot Change' header_name, 0 field_type FROM dual
UNION ALL SELECT  739, 'S01',  721, 'Mazda'         , 0 FROM dual
UNION ALL SELECT  781, 'S01',  739, '2022'          , 0 FROM dual
UNION ALL SELECT  967, 'S01',  781, '1.5L'          , 0 FROM dual
UNION ALL SELECT 1207, 'S01',  967, 'DCT'           , 0 FROM dual
UNION ALL SELECT 2644, 'S01', 1207, '7'             , 0 FROM dual
UNION ALL SELECT 4722, 'S01', 2644, 'FBM4SH15TD7DCT', 9 FROM dual
UNION ALL SELECT 6729, 'S01', 2644, 'FBM4SS15TD7DCT', 9 FROM dual
UNION ALL SELECT 9109, 'S01',  781, '1.8L'          , 0 FROM dual
UNION ALL SELECT 1419, 'S01', 9109, 'MT'            , 0 FROM dual
UNION ALL SELECT 1420, 'S01', 1419, '6'             , 0 FROM dual
UNION ALL SELECT 6731, 'S01', 1420, 'FBM4SS18T6MT'  , 9 FROM dual
UNION ALL SELECT 6732, 'S01', 1420, 'FBM4SH18T6MT'  , 9 FROM dual
;

-- Middle table --
CREATE TABLE test2
AS
SELECT '98D2W11' modelvariant, '1001' feature FROM dual
UNION ALL SELECT '98D2W11' , 'FBM4SH15TD7DCT' FROM dual
UNION ALL SELECT '98D2W11' , '1002'           FROM dual
UNION ALL SELECT '98D2W11' , '1003'           FROM dual
UNION ALL SELECT '99D2W11' , '2300'           FROM dual
UNION ALL SELECT '99D2W11' , '2308'           FROM dual
UNION ALL SELECT '99D2W11' , '2709'           FROM dual
UNION ALL SELECT '99D2W11' , 'FBM4SS15TD7DCT' FROM dual
UNION ALL SELECT '77D2W32' , 'FBM4SS18T6MT'   FROM dual
UNION ALL SELECT '77D2W32' , '6544'           FROM dual
UNION ALL SELECT '77D2W32' , '6545'           FROM dual
UNION ALL SELECT '77D2W32' , '6546'           FROM dual
UNION ALL SELECT '77D2W32' , '6547'           FROM dual
UNION ALL SELECT '88564DFG', '23009'          FROM dual
UNION ALL SELECT '88565DFG', '23012'          FROM dual
UNION ALL SELECT '88566DFG', 'FBM4SH18T6MT'   FROM dual
UNION ALL SELECT '88567DFG', '23056'          FROM dual
UNION ALL SELECT '88568DFG', '23057'          FROM dual
UNION ALL SELECT '88569DFG', '23059'          FROM dual
;

-- Merge --
MERGE INTO test1 a
USING
(
SELECT header_id
     , mug02
     , parent_id
     , header_name
     , 0 field_type
  FROM test1
 WHERE field_type = 9
 UNION ALL
SELECT (SELECT MAX(header_id) FROM test1) + ROWNUM header_id
     , a.mug02
     , a.header_id parent_id
     , b.modelvariant header_name
     , 9 field_type
  FROM test1 a
     , test2 b
 WHERE a.header_name = b.feature
   AND a.field_type = 9
) b
ON (a.header_id = b.header_id)
WHEN MATCHED THEN
  UPDATE SET field_type = b.field_type
WHEN NOT MATCHED THEN
  INSERT VALUES (b.header_id, b.mug02, b.parent_id, b.header_name, b.field_type)
;

-- Result --
SELECT * FROM test1;

오늘도 또 큰도움 받습니다. 감사합니다.