CODEWITHSUNDEEP
sqloracle-databaseinsertmerge
Marcus
Marcus

Reputation: 3869

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. I tried this query but not resulting the latest data.

merge into TEMP_PROCESS_MONITOR_STATISTIC TSM
using (
   select * from
(select ID,PROCESS_MONITOR_ID,PROCESS_START_DATE from 
PROCESS_MONITOR_STATISTIC@FONIC_RETAIL) where rownum<=1000
) data
ON (TSM.ID > data.ID)
when not matched then
insert(ID,PROCESS_MONITOR_ID,PROCESS_START_DATE)
values(data.ID,data.PROCESS_MONITOR_ID,data.PROCESS_START_DATE);

Upvotes: 0

Views: 450

Answers (2)

Ditto
Ditto

Reputation: 3344

As another option ... you could try this:

  INSERT INTO TEMP_PROCESS_MONITOR_STATISTIC
    select ID,PROCESS_MONITOR_ID,PROCESS_START_DATE
      from PROCESS_MONITOR_STATISTIC@FONIC_RETAIL
     where id > (select max(id) from TEMP_PROCESS_MONITOR_STATISTIC );

Restricting it solely based on your largest ID from the target table.

Upvotes: 1

Ditto
Ditto

Reputation: 3344

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.

Upvotes: 1

Related Questions