0

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
moth
  • 1,833
  • 12
  • 29
  • Does this answer your question? [Oracle: how to UPSERT (update or insert into a table?)](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – astentx Dec 29 '22 at 05:07

1 Answers1

3

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)
Paul W
  • 5,507
  • 2
  • 2
  • 13