When doing a MERGE in Oracle SQL, how can I update rows that aren't matched in the SOURCE?

Question

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.

Answer 1

You can do it with a separate UPDATE statement

UPDATE report.TEST target
SET    is Deleted = 'Y'
WHERE  NOT EXISTS (SELECT 1
                   FROM   main.TEST source
                   WHERE  source.ID = target.ID);

I don't know of any way to integrate this into your MERGE statement.

Answer 2

The following answer is to merge data into same table

MERGE INTO YOUR_TABLE d
USING (SELECT 1 FROM DUAL) m
    ON ( d.USER_ID = '123' AND d.USER_NAME= 'itszaif') 
WHEN NOT MATCHED THEN
        INSERT ( d.USERS_ID, d.USER_NAME)
        VALUES ('123','itszaif');

This command checks if USER_ID and USER_NAME are matched, if not matched then it will insert.