db2 merge语句，存在则修改，不存在则插入

最新推荐文章于 2022-07-09 10:52:00 发布

昆虫520

最新推荐文章于 2022-07-09 10:52:00 发布

阅读量1.8k

点赞数

分类专栏：数据库相关

本文链接：https://blog.youkuaiyun.com/weixin_40427212/article/details/95588442

版权

数据库相关专栏收录该内容

3 篇文章

订阅专栏

博客介绍了DB2的Merge语句，该语句具备存在则修改、不存在则插入的功能，在数据库操作中能有效处理数据的更新与插入，提高数据处理效率，是DB2数据库操作的实用语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)