MODIFY...TO COMBINE Examples
1. Insert into table "tab" data from the staging table "tab_insertions":
MODIFY tab UNION tab_insertions TO COMBINE
2. Replace all data in table1 with data from table2:
MODIFY table1 EXCEPT table1 UNION table2 TO COMBINE
3. Keep track of changes in the current product range by creating an intermediate change table:
DROP IF EXISTS product_master;
CREATE TABLE product_master (id_pm INTEGER, name_pm VARCHAR(30), intro_pm DATE);
DROP IF EXISTS product_current;
CREATE TABLE product_current (id_pc INTEGER, name_pc VARCHAR(30));
DROP IF EXISTS change_p;
CREATE TABLE change_p (id_pm INTEGER, name_pm VARCHAR(30), intro_pm DATE);
INSERT INTO product_current VALUES (1, 'Orange Juice'), (2, 'Grape Juice'),(3, 'Grapefruit Juice'),(4, 'Apple Juice'),(5, 'Cranberry Juice'),(6, 'Strawberry Juice'),(7, 'Mango Juice');
SELECT * FROM product_current;
INSERT INTO change_p SELECT pc.id_pc AS id_pm, name_pc AS name_pm, DATE'2011-10-14' AS intro_pm
FROM product_current pc WHERE id_pc NOT IN (SELECT id_pm FROM product_master);
COMMIT;
MODIFY product_master EXCEPT change_p UNION change_p TO COMBINE;
SELECT * FROM product_master;
COMMIT;
INSERT INTO product_current VALUES (8, 'Gooseberry Juice');
UPDATE product_current SET name_pc = 'Carrot Juice' WHERE id_pc = 1;
COMMIT;
SELECT * FROM product_current;
MODIFY change_p EXCEPT change_p TO COMBINE;
INSERT INTO change_p select pc.*, CURRENT_DATE FROM product_current pc
WHERE id_pc NOT IN (SELECT id_pm FROM product_master WHERE name_pc = name_pm);
COMMIT;
SELECT * FROM change_p;
MODIFY product_master EXCEPT change_p UNION change_p TO COMBINE;
SELECT * FROM product_master;