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

User · 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.

User · Answer

merge into x as target using y as Source on target ID   Source ID when not matched by target then insert when matched then update when not matched by source and target ID is not null then update whatevercolumn    isdeleted

User · Answer

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

User · Answer

MERGE INTO target USING       --Source data     SELECT id  some value  0 deleteMe FROM source     --And anything that has been deleted from the source     UNION ALL     SELECT id  null some value  1 deleteMe     FROM               SELECT id FROM target         MINUS         SELECT id FROM source         source    ON  target ID   source ID  WHEN MATCHED THEN     --Requires a lot of ugly CASE statements  to prevent updating deleted data     UPDATE SET target some value           CASE WHEN deleteMe 1 THEN target some value ELSE source some value end      isDeleted   deleteMe WHEN NOT MATCHED THEN     INSERT  id  some value  isDeleted  VALUES  source id  source some value  0   --Test data create table target as select 1 ID   old value 1  some value  0 isDeleted from dual union all select 2 ID   old value 2  some value  0 isDeleted from dual   create table source as select 1 ID   new value 1  some value  0 isDeleted from dual union all select 3 ID   new value 3  some value  0 isDeleted from dual    --Results  select   from target   ID  SOME VALUE   ISDELETED 1   new value 1  0 2   old value 2  1 3   new value 3  0

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

Examples related to sql

Examples related to oracle

Examples related to sql-merge