I have below merge query which is working fine and perform delta logic but not completely.
In this Merge query i am trying to perform Delta import logic using Insert/Update/Delete. For new data with combination of Unique column(ID_LL_ ID_UU, TKR) i want to insert data into TEST_RUA_MER table otherwise update data. Delete will delete the data from test_rua_mer table which does not exist in test_rua table.
But now i want change the logic in this merge query. I dont want to delete the data. The logic for Update and Insert will be same. I want to create for example new field say "Delta_Date" of Date datatype in TEST_RUA_MER table where based on the comparision of table TEST_RUA , i want to update this field with the current date for all the rows for which the data has been updated or inserted in table TEST_RUA_MER. So that whenever i query on this table i can simple use this "Delta_Date" with the current date to recognize which and how many data has been Inserted or Updated.
For the matching row between table TEST_RUA_MER and TEST_RUA we will not do anything.
As we are using Union All in Merge query, if required we can also create dummy field same like "Delta_Date" of Date datatype in TEST_RUA table.
This question is extending my previous question where i mentioned the datasets::
undefined (0 Bytes)Below is the Merge query :
merge into test_rua_mer d
using (
select a.*
, case
when target = 'D' and cnt_id = cnt_vals then 'D'
when target = 'S' and cnt_id = 1 and cnt_vals = 1 then 'I'
when target = 'S' and cnt_id = 2 and cnt_vals = 1 then 'U'
else 'X' -- ignore
end action
from (
select
count(*) over (partition by id_ll, id_uu, tkr) cnt_id
, count(*) over (partition by id_ll, id_uu, tkr, class, name) cnt_vals
, max(rwid) over (partition by id_ll, id_uu, tkr) rwid
, class, id_ll, id_uu, tkr, name, target
from (
select class, id_ll, id_uu, tkr, name, 'D' target, rowid rwid
from test_rua_mer
union all
select class, id_ll, id_uu, tkr, name, 'S' , null
from test_rua
) a
) a
) s
on (d.rowid = s.rwid)
when not matched then
insert (d.class, d.id_ll, d.id_uu, d.tkr, d.name)
values (s.class, s.id_ll, s.id_uu, s.tkr, s.name)
where action = 'I'
when matched then
update set
d.class = s.class,
d.name = s.name
where action in ('D', 'U')
delete where action = 'D';
Below is the data set in fiddle :
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=867a10686b20aa45c9914ff36b46d015