0

The following Merge statement is not Inserting (going to NOT MATCHED) the record.

The query used in the Condition part of the Merge clause sometimes will not return any row.

Is this causing the Insert to not happen? 

MERGE INTO apps.test_table ab USING
(SELECT batch_id
FROM apps.test_table
WHERE session_name='session_name'
AND status        ='NOT STARTED'
)b ON (ab.batch_id=b.batch_id)
WHEN MATCHED THEN
  UPDATE
  SET STATUS         ='RUNNING',
    tag_receive_time = sysdate,
    sess_start_time  = SYSDATE,
    wflw_start_time  = sysdate
  WHERE batch_id     = b.batch_id 
WHEN NOT MATCHED THEN
  INSERT
    (
      ab.batch_id,
      ab.status,
      ab.workflow_name,
      ab.session_name,
      ab.source_name,
      ab.target_table,
      ab.created_by,
      ab.creation_date,
      ab.snapshot_date,
      ab.tag_receive_time,
      ab.sess_start_time,
      ab.wflw_start_time
    )
    VALUES
    (
      apps.batch_id_seq.nextval,
      'RUNNING',
      '$PMWorkflowName',
      '$PMSessionName',
      'AMPS',
      'SPARES_F_ORDER_SHIPMENT_DTL',
      'Informatica',
      SYSDATE,
      SYSDATE,
      SYSDATE,
      SYSDATE,
      SYSDATE
    )
    ;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Gautam Hans
  • 135
  • 1
  • 12
  • please provide sample data for table apps.test_table – Andrew Nov 04 '19 at 10:25
  • what do you see when `show errors` issued ? Btw, remove `WHERE batch_id = b.batch_id ` part from the Update statement. – Barbaros Özhan Nov 04 '19 at 10:29
  • Your merge statement is saying: "find me these specific rows in the apps.test_table and then if they match to a row in the apps.test_table, update them otherwise insert them." Do you see the issue there? You're looking for a subset of rows that exist in the table... of course your source subquery isn't going to find extra rows that aren't already in the apps.test_table! Your `when not matched` part of that merge statement is irrelevant and you should remove it. – Boneist Nov 04 '19 at 11:00

2 Answers2

0

Breakdown you query to see what results are coming..Run and check the query result from below Select query :

SELECT batch_id FROM apps.test_table WHERE session_name='session_name' AND 
status ='NOT STARTED'

And the column batch_id result of the above query contains in table apps.test_table or not if not then it should Insert into table apps.test_table else your Update statement will work

Remove WHERE batch_id = b.batch_id from Update as its not needed.

Andrew
  • 3,632
  • 24
  • 64
  • 113
0

You already check matching for Update statement within ON (ab.batch_id=b.batch_id), then need to remove WHERE batch_id = b.batch_id after the Update statement.

You can check this out,as sample syntaxes, where neither of the Update statements contain a where clause

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55