如果你的需求满足:根据条件如果满足则对该条数据进行修改操作,如果不满足则进行新增操作。
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);