Reputation: 41
I have a source and target table as below:
Scenario:
I tried to write Merge statement but getting error that MATCHED clause in MERGE statement must be followed by UPDATE or DELETE clause.
merge into target_table as tgt
using source_table as src
on src.id = tgt.id
when not matched then
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
update set id = src.id, value1 = src.value1, value2 = src.value2, end_date = get_date(), active_flag = 'N'
when matched and (src.value1 != tgt.value1 or src.value2 != src.value2) then
insert (id, value1, value2, end_date, active_flag) values (src.id, src.value1, src.value2, null, 'Y')
How can I achieve the desired output using MERGE statement or custom query?
Upvotes: 1
Views: 1131