Using Insert/Update with 2 Tables - Oracle Apex

Question

I'm still new to SQL and am having a hard time figuring out how to update and insert from one table into another table in Oracle Apex (uses SQL).

The two tables are named Temp and Table (example) and both have the same columns. Basically they are copies of each other but with different data. I would like to compare the ID field in Temp to the ID field in Table and if there is a row that matches that ID field in Temp, then to overwrite all the data on the row in Table with the data in the corresponding row in Temp.

Note: Table has 10 million rows of data, Temp has like 500.

if Temp.ID = Table.ID then
update
      set 
      Table.ID = Temp.ID
      Table.Address = Temp.Address
else
insert
      (Table.ID,
        Table.Address)
   values
      (Temp.ID,
        Temp.Address

That is basically what I want done but not sure how to write it in SQL. Have seen a lot of different answers, but none really involving 2 tables and mostly for MySQL or SQL Server specific SQL that I am not sure also works with Oracle.

MERGE into TEST1
USING TEST2 on (TEST2.ID = TEST1.ID)
WHEN matched THEN UPDATE 
SET TEST1.ID = TEST2.ID, TEST1.NAME = TEST2.NAME, TEST1.ADDRESS = TEST2.ADDRESS, TEST1.EMAIL = TEST2.EMAIL
WHEN not matched THEN INSERT (ID, NAME, ADDRESS, EMAIL) values (TEST2.ID, TEST2.NAME, TEST2.ADDRESS, TEST2.EMAIL);

I tried this but it is giving me the error:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "TEST1"."ID"

UPDATE: Got it to work! http://db-oriented.com/2013/09/20/the-merge-statement/

Was helpful with the answer to the error and I did not know that you could not have the IDs in the update clause, which makes total sense now. Thank you also to the respondent below for explaining the merge code to me. :)

Littlefoot · Accepted Answer · 2019-08-08 19:24:59Z

2

Looks like a good candidate for MERGE. Have a look at the example.

Sample tables & data:

SQL> create table ttable (id number, address varchar2(20));

Table created.

SQL> create table temp   (id number, address varchar2(20));

Table created.

SQL> insert into ttable
  2    select 1, 'Address 1' from dual union all
  3    select 2, 'Address 2' from dual union all
  4    select 3, 'Address 3' from dual;

3 rows created.

SQL> insert into temp
  2    select 1, 'New address 1' from dual union all
  3    select 2, 'New address 2' from dual union all
  4    select 4, 'New address 4' from dual;

3 rows created.

Merge & the result:

SQL> merge into ttable a
  2    using temp e on (e.id = a.id)
  3    when matched then update set a.address = e.address
  4    when not matched then insert (id, address) values (e.id, e.address);

3 rows merged.

SQL> select * from ttable;

        ID ADDRESS
---------- --------------------
         1 New address 1
         2 New address 2
         3 Address 3
         4 New address 4

SQL>

answered Aug 8, 2019 at 19:24

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madeirey Over a year ago

Just for education purposes/my full understanding, can you explain what the "ttable a" and "temp e" are for and why it isn't just labeled (temp.id = ttable.id)?

Thomas Tschernich Over a year ago

These are just table aliases and used to shorten or abbreviate the table name. (temp.id = ttable.id) should work as well.

Collectives™ on Stack Overflow

Using Insert/Update with 2 Tables - Oracle Apex

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related