天天看點

db2 merge語句,存在則修改,不存在則插入

MERGE INTO
	UWP_CUST.T_CUST_RELATION C1
		USING(
	SELECT
		? AS AREA_CODE,
		? AS CIS,
		? AS CUST_NAME,
		? AS CIS_NO,
		? AS MANAGE_SOURCE,
		? AS ALLOCATION_STATUS,
		? AS CREATE_ID,
		CURRENT_TIMESTAMP AS CREATE_TIME,
		? AS UPDATE_ID,
		CURRENT_TIMESTAMP AS UPDATE_TIME 
	FROM
		SYSIBM.SYSDUMMY1) C2 ON
	(C1.CIS = C2.CIS AND C1.AREA_CODE = C2.AREA_CODE)
	WHEN MATCHED THEN UPDATE
	SET
		C1.CUST_NAME = C2.CUST_NAME,
        C1.CIS_NO = C2.CIS_NO,
        C1.MANAGE_SOURCE = C2.MANAGE_SOURCE
		WHEN NOT MATCHED THEN INSERT
			(ID,
			USER_ID,
			AREA_CODE,
			CIS,
			CUST_NAME,
			CIS_NO,
			MANAGE_SOURCE,
			ALLOCATION_STATUS,
			CREATE_ID,
			CREATE_TIME)
		VALUES ( UWP_CUST.SEQ_T_CUST_RELATION.NEXTVAL,
		(SELECT USER_ID FROM UWP_CUST.T_CRM_CUST WHERE CIS = C2.CIS AND AREA_CODE = C2.AREA_CODE),
		C2.AREA_CODE,
		C2.CIS,
		C2.CUST_NAME,
		C2.CIS_NO,
		C2.MANAGE_SOURCE,
		C2.ALLOCATION_STATUS,
		C2.CREATE_ID,
		C2.CREATE_TIME)
           

繼續閱讀