update and insert from another table based on date and column id-CodePudding

Say I have two tables test and test2, here is the data:

create table test (id number primary key, name varchar2(20), insertion_date date);
create table test2 (id number primary key, name varchar2(20), insertion_date date);

insert into test values (1,'Jay','05-Jan-19');
insert into test values (2,'John','05-Jan-20');

insert into test2 values (1,'Jay','05-Mar-25');
insert into test2 values (2,'John','05-Mar-25');
insert into test2 values (3,'Maria','05-Mar-22');

It looks like that:

test

1   Jay     05-JAN-19
2   John    05-JAN-20

test2

1   Jay     05-MAR-25
2   John    05-MAR-25
3   Maria   05-MAR-22

I want to update the row in test if the insertion_date column in the test2 table is in the future of insertion_date of test table and I want to insert every row that has an id from test2 which is not present in test.

I tried this for the update clause:

   update test
set name = case when test.insertion_date < test2.insertion_date then test2.name else test.name end,
    insertion_date = case when test.insertion_date < test2.insertion_date then test2.insertion_date else test.insertion_date end,
where test.id = test2.id;

But getting this error:

Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 -  "invalid user.table.column, table.column, or column specification"
*Cause:    
*Action:

For the insert column I got it working by doing this:

insert into test select * from test2 where id not in (select id from test);

However I was wondering if there is a better way to update and insert in one single clause.

the desired result is this:

test

1   Jay     05-MAR-25
2   John    05-MAR-25
3   Maria   05-MAR-22

CodePudding user response：

Use MERGE.

merge into test
using test2
on (test.id = test2.id)
when matched then update test.insertion_date = test2.insertion_date                            
                   where test2.insertion_date > test.insertion_date
when not matched then insert (id,name,insertion_date)
                       values (test2.id,test2.name,test2.insertion_date)