HI Team,
I having one target table
select * from CRC.IM_REQ_INDEX_CONT_T
Eg rows :
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
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