Hi,
I have the below table.
source
| id | value | start_date | end_date |
| 1 | 100 | 3/11/2019 | 12/31/8888 |
| 2 | 200 | 3/11/2019 | 12/31/8888 |
| 3 | 300 | 3/11/2019 | 12/31/8888 |
| 4 | 400 | 3/11/2019 | 12/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
the final value in the source table will be as below..
| id | value | start_date | end_date |
| 1 | 100 | 3/11/2019 | 3/16/2019 |
| 2 | 200 | 3/11/2019 | 12/31/8888 |
| 3 | 300 | 3/11/2019 | 12/31/8888 |
| 4 | 400 | 3/11/2019 | 12/31/8888 |
| 1 | 1000 | 3/16/2019 | 12/31/8888 |
| 5 | 500 | 3/16/2019 | 12/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?