Merge Data into IDR.PRESC_TPL Table Using Merge Statement

merge into idr.PRESC_TPL T1
using (
<foreach collection="prescList" item="item" separator="union">
select
#{item.prescTplId} as PRESC_TPL_ID,
#{item.dataVersion} as DATA_VERSION,
#{item.hospitalId} as HOSPITAL_ID,
#{item.tplName} as TPL_NAME,
#{item.sortId} as SORT_ID,
#{item.createDeptId} as CREATE_DEPT_ID,
#{item.createPersonId} as CREATE_PERSON_ID,
#{item.prescType} as PRESC_TYPE,
#{item.prescDiag} as PRESC_DIAG,
#{item.drugDept} as DRUG_DEPT,
#{item.fromType} as FROM_TYPE,
#{item.prescDiagId} as PRESC_DIAG_ID,
#{item.prescCategory} as PRESC_CATEGORY,
#{item.parentTplId} as PARENT_TPL_ID,
#{item.keyWordOne} as KEY_WORD_ONE,
#{item.keyWordTwo} as KEY_WORD_TWO,
#{item.quasiDiagnosisId} as QUASI_DIAGNOSIS_ID
from dual
</foreach>
) T2
on (T1.PRESC_TPL_ID = T2.PRESC_TPL_ID)
when matched then
update set
gmt_modify = CURRENT_TIMESTAMP,
DATA_VERSION = T1.DATA_VERSION + 1,
HOSPITAL_ID = T2.HOSPITAL_ID,
TPL_NAME = T2.TPL_NAME,
SORT_ID = T2.SORT_ID,
CREATE_DEPT_ID = T2.CREATE_DEPT_ID,
CREATE_PERSON_ID = T2.CREATE_PERSON_ID,
PRESC_TYPE = T2.PRESC_TYPE,
PRESC_DIAG = T2.PRESC_DIAG,
DRUG_DEPT = T2.DRUG_DEPT,
FROM_TYPE = T2.FROM_TYPE,
PRESC_DIAG_ID = T2.PRESC_DIAG_ID,
PRESC_CATEGORY = T2.PRESC_CATEGORY,
PARENT_TPL_ID = T2.PARENT_TPL_ID,KEY_WORD_ONE = T2.KEY_WORD_ONE,KEY_WORD_TWO = T2.KEY_WORD_TWO,
QUASI_DIAGNOSIS_ID = T2.QUASI_DIAGNOSIS_ID
when not matched then
insert (
PRESC_TPL_ID,  DATA_VERSION, GMT_CREATE, GMT_MODIFY, HOSPITAL_ID,  TPL_NAME, SORT_ID, CREATE_DEPT_ID,
CREATE_PERSON_ID, PRESC_TYPE, PRESC_DIAG, DRUG_DEPT,  FROM_TYPE, PRESC_DIAG_ID, PRESC_CATEGORY,
DEL_FLAG, PARENT_TPL_ID,KEY_WORD_ONE , KEY_WORD_TWO,QUASI_DIAGNOSIS_ID
)
values (
        T2.PRESC_TPL_ID,  1,sysdate, sysdate, T2.HOSPITAL_ID, T2.TPL_NAME,
        T2.SORT_ID, T2.CREATE_DEPT_ID, T2.CREATE_PERSON_ID, T2.PRESC_TYPE, T2.PRESC_DIAG,
        T2.DRUG_DEPT,  T2.FROM_TYPE, T2.PRESC_DIAG_ID, T2.PRESC_CATEGORY,  '0', T2.PARENT_TPL_ID,
        T2.KEY_WORD_ONE,T2.KEY_WORD_TWO, T2.QUASI_DIAGNOSIS_ID
)

Explanation:

• Merge Statement: This SQL statement combines both UPDATE and INSERT operations based on matching conditions.
• Target Table: idr.PRESC_TPL is the target table where data will be updated or inserted.
• Source Data: The using clause defines the source data for the merge operation. It uses a foreach loop to iterate over a collection named prescList and dynamically generates a select statement for each item in the collection.
• Matching Condition: The on clause specifies the condition for matching rows between the target table (T1) and the source data (T2). In this case, it uses the PRESC_TPL_ID column.
• Update Clause: The when matched then clause defines the update operation if a match is found between the target table and the source data. It updates various columns based on the corresponding values in the source data, including DATA_VERSION, HOSPITAL_ID, TPL_NAME, etc.
• Insert Clause: The when not matched then clause defines the insert operation if no match is found. It inserts a new row into the target table with values from the source data. The DEL_FLAG column might need additional attention as it is not explicitly set in the select statement. Also, the DATA_VERSION for the newly inserted row is set to 1, which might need adjustment based on the application's requirements.

Note: The provided code snippet is a general example. It might require further adjustments depending on the actual data types, constraints, and business logic of the specific database table and application.