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.

With which values do you want to update `main.test`? Where would you get them from? — , May 10 '12 at 18:09
If the row is deleted from `main.test`, then the corresponding `report.test` row will be unmatched. I want to set the `report.test.IsDeleted` flag for all unmatched rows. — Scott Rippey, May 10 '12 at 18:29
I don't think that will be possible as the result of the underlying JOIN (`using (...)`) will not contain that row. So where should that data come from? — , May 10 '12 at 18:30
So, is `MERGE` similar to an `LEFT OUTER JOIN`? I guess I was hoping that might have a `FULL OUTER JOIN` option. — Scott Rippey, May 10 '12 at 18:34
I think that SQL Server 2008 has a `WHEN NOT MATCHED ON SOURCE` clause that does a `FULL OUTER JOIN`! I can't find much documentation on it, but I tried it in Oracle with no success, so I guess I'll look at other options. — Scott Rippey, May 10 '12 at 18:49

score 19 · Accepted Answer · answered May 10 '12 at 18:20

19

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.

answered May 10 '12 at 18:20

steve godfrey

1,234
7
14

It seems weird that I can't do this with MERGE. I'll consider this answer. – Scott Rippey May 10 '12 at 18:31
Would it be any more "elegant"/efficient if I did an `OUTER JOIN`, and updated where `main.test is null`? – Scott Rippey May 10 '12 at 18:32
1

There's a related question here http://stackoverflow.com/questions/4863960/could-somebody-explain-what-the-merge-statement-really-does-in-oracle, I think it'll do an outer join, not a full outer join, so I don't think what you've suggested will work. – steve godfrey May 10 '12 at 18:41
4

Thank you so much, that link was very helpful What I learned: if `MERGE` only has `WHEN MATCHED THEN`, it uses a regular `JOIN`. If there's a `WHEN NOT MATCHED THEN`, it uses a `LEFT OUTER JOIN`. Now, I'm wishing for a feature such as `WHEN NOT MATCHED ON SOURCE THEN`, which will do a `FULL OUTER JOIN`. But this might only exist for SQL Server, not Oracle. – Scott Rippey May 10 '12 at 18:47

score 6 · Answer 2 · answered May 16 '17 at 15:08

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.

score 5 · Answer 3 · answered May 11 '12 at 18:34

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

Thanks for being creative! I'm not gonna do this, but it does get the job done. — Scott Rippey, May 14 '12 at 18:38

score 0 · Answer 4 · edited Oct 17 '12 at 09:32

0

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' ;

edited Oct 17 '12 at 09:32

Florin Ghita

17,525
6
57
76

answered Oct 17 '12 at 07:17

Mark

218
1
4
12

where did you found this syntax? (`by target`) – Florin Ghita Oct 17 '12 at 09:33
This is exactly what I'm looking for ... however, as @FlorinGhita said, where did you find this syntax? Is this MS SQL syntax? – Scott Rippey Oct 18 '12 at 19:27
9

-1 as this is [SQL Server merge](http://msdn.microsoft.com/en-us/library/bb510625.aspx) syntax not [Oracle merge](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606) – user272735 Mar 05 '14 at 13:07
4

Just for everyone's reference, this is a good answer, but `not matched by target` and `not matched by source` is a feature only supported by MS SQL Server. This feature would solve my problem, so I wish Oracle SQL supported it. – Scott Rippey Mar 13 '14 at 17:04

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

4 Answers4

Linked