Source table:
HEADER_ID MUG02 PARENT_ID HEADER_NAME FIELD_TYPE
721 S01 -1 Pilot Change 0
739 S01 721 Mazda 0
781 S01 739 2022 0
967 S01 781 1.5L 0
1207 S01 967 DCT 0
2644 S01 1207 7 0
4722 S01 2644 FBM4SH15TD7DCT 9
6729 S01 2644 FBM4SS15TD7DCT 9
9109 S01 781 1.8L 0
1419 S01 9109 MT 0
1420 S01 1419 6 0
6731 S01 1420 FBM4SS18T6MT 9
6732 S01 1420 FBM4SH18T6MT 9
Middle table:
ModelVariant Feature
98D2W11 1001
98D2W11 FBM4SH15TD7DCT
98D2W11 1002
98D2W11 1003
99D2W11 2300
99D2W11 2308
99D2W11 2709
99D2W11 FBM4SS15TD7DCT
77D2W32 FBM4SS18T6MT
77D2W32 6544
77D2W32 6545
77D2W32 6546
77D2W32 6547
88564DFG 23009
88565DFG 23012
88566DFG FBM4SH18T6MT
88567DFG 23056
88568DFG 23057
88569DFG 23059
Result table:
HEADER_ID MUG02 PARENT_ID HEADER_NAME FIELD_TYPE
721 S01 -1 Pilot Change 0
739 S01 721 Mazda 0
781 S01 739 2022 0
967 S01 781 1.5L 0
1207 S01 967 DCT 0
2644 S01 1207 7 0
4722 S01 2644 FBM4SH15TD7DCT 0
6729 S01 2644 FBM4SS15TD7DCT 0
9109 S01 781 1.8L 0
1419 S01 9109 MT 0
1420 S01 1419 6 0
6731 S01 1420 FBM4SS18T6MT 0
6732 S01 1420 FBM4SH18T6MT 0
XXX001 S01 4722 98D2W11 9
XXX002 S01 6729 99D2W11 9
XXX003 S01 6731 77D2W32 9
XXX004 S01 6732 88566DFG 9
** 신규로 생성되는 Header_id는 새로 생기는 것이라 sysdate를 이용것이든 unique하기만 하면 됩니다.
항상 감사합니다.
-- 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;