Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
5,184 views