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.
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.
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.
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' ;
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
Source: Stackoverflow.com