When doing a MERGE in Oracle SQL, how can I update rows that aren't matched in the SOURCE?
Solution 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.
Solution 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.
Solution 3
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
Scott Rippey
I love solving puzzles, finding patterns, and writing software. Which are pretty much all the same thing.
Updated on May 17, 2020Comments
-
Scott Rippey about 4 years
I have a
main
database and areport
database, and I need to sync a table frommain
intoreport
.However, when an item gets deleted in the
main
database, I only want to set anIsDeleted
flag in thereport
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 frommain
, but I also want to update all OLD values fromreport
.I'm using Oracle PL/SQL.
-
Scott Rippey about 12 yearsIt seems weird that I can't do this with MERGE. I'll consider this answer.
-
Scott Rippey about 12 yearsWould it be any more "elegant"/efficient if I did an
OUTER JOIN
, and updated wheremain.test is null
? -
steve godfrey about 12 yearsThere's a related question here stackoverflow.com/questions/4863960/…, I think it'll do an outer join, not a full outer join, so I don't think what you've suggested will work.
-
Scott Rippey about 12 yearsThank you so much, that link was very helpful What I learned: if
MERGE
only hasWHEN MATCHED THEN
, it uses a regularJOIN
. If there's aWHEN NOT MATCHED THEN
, it uses aLEFT OUTER JOIN
. Now, I'm wishing for a feature such asWHEN NOT MATCHED ON SOURCE THEN
, which will do aFULL OUTER JOIN
. But this might only exist for SQL Server, not Oracle. -
Scott Rippey about 12 yearsThanks for being creative! I'm not gonna do this, but it does get the job done.
-
Florin Ghita over 11 yearswhere did you found this syntax? (
by target
) -
Scott Rippey over 11 yearsThis is exactly what I'm looking for ... however, as @FlorinGhita said, where did you find this syntax? Is this MS SQL syntax?
-
user272735 about 10 years-1 as this is SQL Server merge syntax not Oracle merge
-
Scott Rippey about 10 yearsJust for everyone's reference, this is a good answer, but
not matched by target
andnot 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.