I have a main database and a report database, and I need to sync a table from main into report.
However, when an item gets deleted in the main database, I only want to set an IsDeleted flag in the report database.
What is an elegant way to do this?
I'm currently using a MERGE statement, like this:
MERGE INTO report.TEST target
USING (SELECT * FROM main.TEST) source
ON (target.ID = source.ID)
WHEN MATCHED THEN
UPDATE SET (target... = source...)
WHEN NOT MATCHED THEN
INSERT (...) VALUES (source...)
The WHEN NOT MATCHED statement gives me all NEW values from main, but I also want to update all OLD values from report.
I'm using Oracle PL/SQL.