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.

Accepted Answer

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.

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

Solution

Problem

Related problems