Skip to Main Content

update,insert and delete in single merge query

3521116Apr 25 2019 — edited Apr 25 2019

HI Team,

I having one target table

select * from CRC.IM_REQ_INDEX_CONT_T

Eg rows :

pastedImage_0.png

I having a source table with multiple joins

SELECT a.inscon_no indcont_no,

  a.inscon_ind_no ind_no,

  a.inspty_no indcont_type_no,

  d.type_id indcont_type_name,

  a.inscon_key_1 indcont_key_1,

  a.inscon_key_2 indcont_key_2,

  a.inscon_key_3 indcont_key_3,

  a.inscon_key_4 indcont_key_4

FROM bisa1.inscon_work_t a,

  crc.im_req_index_t c,

  crc.short_doctype d,

  bisa1.infospec_type_t e

WHERE a.inscon_ind_type = 'REQ'

AND a.inscon_ind_no     = c.ind_no

AND e.inspty_code_docs  = d.shortdoctype

AND a.inspty_no         = e.inspty_no

AND a.W_DELETE_DATE      IS NULL

AND c.delete_date      IS NULL

AND d.delete_date      IS NULL

AND e.delete_date      IS NULL

EG records

pastedImage_2.png

Now my scenario is

INDCOUNT_NO as PK key with that key i used to perform all the operations

1. i want move the records from source to target if its matched i need to update Reg_date and Upd_date column as sysdate and delete date as null

2. If its unmatched i need to insert in target table with update Reg_date and Upd_date column as sysdate and delete date as null

3. In target table i have some  extras records that i need to update delete_date as sysdate

Below is the Merge statement i tried . First two scenarios is working fine. How to perform 3rd scenario

merge into CRC.IM_REQ_INDEX_CONT_T x

using (

SELECT a.inscon_no indcont_no,

  a.inscon_ind_no ind_no,

  a.inspty_no indcont_type_no,

  d.type_id indcont_type_name,

  a.inscon_key_1 indcont_key_1,

  a.inscon_key_2 indcont_key_2,

  a.inscon_key_3 indcont_key_3,

  a.inscon_key_4 indcont_key_4

FROM bisa1.inscon_work_t a,

  crc.im_req_index_t c,

  crc.short_doctype d,

  bisa1.infospec_type_t e

WHERE a.inscon_ind_type = 'REQ'

AND a.inscon_ind_no     = c.ind_no

AND e.inspty_code_docs  = d.shortdoctype

AND a.inspty_no         = e.inspty_no

AND a.W_DELETE_DATE      IS NULL

AND c.delete_date      IS NULL

AND d.delete_date      IS NULL

AND e.delete_date      IS NULL

) y

on (x.INDCONT_NO = y.indcont_no)

when matched then

update set x.REG_DATE=sysdate,x.UPD_DATE=sysdate,x.DELETE_DATE=null

when not matched then

insert (x.INDCONT_NO,x.IND_NO,x.INDCONT_TYPE_NO,x.INDCONT_TYPE_NAME,x.INDCONT_KEY_1,x.INDCONT_KEY_2,x.INDCONT_KEY_3,x.INDCONT_KEY_4,x.REG_DATE,x.UPD_DATE,x.DELETE_DATE)

values (y.indcont_no,y.ind_no,y.indcont_type_no,y.INDCONT_TYPE_NAME,y.INDCONT_KEY_1,y.INDCONT_KEY_2,y.INDCONT_KEY_3,y.INDCONT_KEY_4,sysdate,sysdate,null)

Please help

Comments
Post Details
Added on Apr 25 2019
1 comment
2,336 views