Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Issue with MERGE Statement on same table with the table being empty for first time

User_3ZQRHSep 26 2022 — edited Sep 26 2022

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.....

This post has been answered by Solomon Yakobson on Sep 26 2022
Jump to Answer
Comments
Post Details
Added on Sep 26 2022
6 comments
5,953 views