Andrew Andrew - 4 months ago 13
SQL Question

Merge statement not matched with the ON Clause in oracle sql

I have oracle sql merge statement query in which i want to merge the data from

PROCESS_MONITOR_STATISTIC
to
TEMP_PROCESS_MONITOR_STATISTIC
table.

The ID are sequence generated in
PROCESS_MONITOR_STATISTIC
table. So i always want to get the data into
TEMP_PROCESS_MONITOR_STATISTIC
table with the ID greater than the ID from
PROCESS_MONITOR_STATISTIC
. So that i will always want to insert the latest data into the
TEMP_PROCESS_MONITOR_STATISTIC
table when i execute the query.

Answer

If all you're doing is "inserting items into table B from A, which don't already exist".

This should do it:

  INSERT INTO TEMP_PROCESS_MONITOR_STATISTIC
    select ID,PROCESS_MONITOR_ID,PROCESS_START_DATE
      from PROCESS_MONITOR_STATISTIC@FONIC_RETAIL
     minus 
       select ID,PROCESS_MONITOR_ID,PROCESS_START_DATE
         from TEMP_PROCESS_MONITOR_STATISTIC;

No MERGE needed.

Comments