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!

merge statement help - single update and multiple insert

user13445603Mar 16 2019 — edited Mar 17 2019

Hi,

I have the below table.

source

    

idvaluestart_dateend_date
11003/11/201912/31/8888
22003/11/201912/31/8888
33003/11/201912/31/8888
44003/11/201912/31/8888

I have to update and insert the above table based on the below table.. the date 12/31/8888 means its a active row and start data is always sysdate when the row is inserted.

temp

  

idvalue
11000
2200
5500

the final value in the source table will be as below..

    

idvaluestart_dateend_date
11003/11/20193/16/2019
22003/11/201912/31/8888
33003/11/201912/31/8888
44003/11/201912/31/8888
110003/16/201912/31/8888
55003/16/201912/31/8888

the blue cell is the update and green cells are the inserted data.

explanation : id (1,2,5) is present in temp table and values are (1000,200,500). So, id=1 is updated for end date = sysdate and a new row is inserted for a different value with 1000.

id=5 is a new insert.

id=2 is no effect in the merge as the value is same in both the table.

Is the above situation can be handled by a single merge statement?

Comments
Post Details
Added on Mar 16 2019
10 comments
3,779 views