How to insert data into a table from another table with the possibility of duplicate keys

Question

I'm attempting to write a conditional query that inserts new rows into a table from data from another table, but if the PK already exists for that row, to update it instead. I came across ON DUPLICATE KEY UPDATE, but can only find examples that work with inserted values rather than data obtained from an inner select statement. Here is an example to illustrate what I'm talking about:

INSERT INTO SCHEMA1.TABLE1 T1
  (T1.ID,
   T1.COLUMN1,
   T1.COLUMN2)
SELECT T2.ID,
       T2.COLUMN1,
       T2.COLUMN2
FROM SCHEMA2.TABLE2 T2
WHERE T2.COLUMN3 = ?)
ON DUPLICATE KEY UPDATE T1.COLUMN1 = T2.COLUMN1;

For some reason, the syntax of the above does not work. The only thing that I can gather from the Oracle docs is that ON DUPLICATE KEY UPDATE can only be used with VALUES -- not a nested SELECT query.

Answer 1

Thanks to mustaccio and Alex Poole, here is the solution:

MERGE INTO SCHEMA1.TABLE1 T1 USING
(SELECT ID,
   COLUMN1,
   COLUMN2
 FROM SCHEMA2.TABLE2
 WHERE COLUMN3 = ?) T2
ON (T1.ID = T2.ID)
WHEN MATCHED THEN UPDATE SET
  T1.COLUMN1 = T2.COLUMN1,
  T1.COLUMN2 = T2.COLUMN2
WHEN NOT MATCHED THEN INSERT (
  T1.ID,
  T1.COLUMN1,
  T1.COLUMN2
VALUES (
  T2.ID,
  T2.COLUMN1,
  T2.COLUMN2);