ORACLE的数据新增与修改兼顾操作merge into

SQL合并插入与更新

最新推荐文章于 2025-08-08 00:40:04 发布

原创最新推荐文章于 2025-08-08 00:40:04 发布 · 477 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #ibatis

如果你的需求满足：根据条件如果满足则对该条数据进行修改操作，如果不满足则进行新增操作。

sql形式如下：

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
	[UPDATE sql]
WHEN NOT MATCHED THEN
	[INSERT sql]

sql实例如下：

<insert id="insertTwesd00OrUpdate">

    MERGE INTO iplat_logistics.TWESD04 A
	       USING (SELECT #batchNo# batchNo,#warehouseNo# warehouseNo FROM DUAL) B
		   ON  (A.BATCH_NO=B.batchNo AND A.WAREHOUSE_NO=B.warehouseNo)
	WHEN MATCHED THEN
			  UPDATE SET
			            STORAGE_NO = STORAGE_NO + #storageNo#,
						AVAILABLE_NO = AVAILABLE_NO + #storageNo#,
						SURPLUS_NO = SURPLUS_NO + #storageNo#
			  WHEN NOT MATCHED THEN
			            INSERT(
			                BATCH_NO,
			                WAREHOUSE_NO,
			                STOCK_ROOM_NO,
			                STORAGE_NO,
			                AVAILABLE_NO,
			                SURPLUS_NO
			            )VALUES(
			                
                  #batchNo#,#warehouseNo#,#stockRoomNo#,#storageNo#,#storageNo#,#storageNo#
			            )
</insert>

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID)
WHEN NOT MATCHED THEN
	insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);