I am working on Oracle 19c and my requirement is to have merge statement which will Insert the first time when the table is empty but from next time will update (if there is same existing record) or will insert (if it is a new record).
The update-insert is working fine when there is already a record in the table but 0 record is getting merged when the table is empty in the first instance
I understand it is because it is null in the USING subquery in the first instance but but I am struggling how to use DUAL. Because when I am hardcoding values using DUAL in the USING sub query then records are always getting inserted ! :(
This is my merge statement. Can anyone please help me ?
Thanks in advance.
MERGE into Table 1 src
using ( select col 1, col2, col3 from Table 1) tgt
on src.col1 = tgt.col1 and src.col2 = tgt.col2 and src.col3 = tgt.col3
WHEN MATCHED THEN UPDATE....
WHEN NOT MATCHED THEN INSERT.....