数据库表数据存在更新不存在插入的SQL语句

1、postgres数据库

INSERT INTO table (clumn1,clumn2)

VALUES (values1,values2)

ON conflict (clumn1)

DO UPDATE SET climn1='values' ,clumn2='value2'

直接上图

在mybatis的mapper里面酱紫写

<insert id="insert" parameterType="com.baosight.etl.entity.JobMonitor"> 
insert into "${tenant}".j_job_monitor(
 <if test="jobId != null and jobId != ''">
 job_id 
</if> 
<if test="jobStartTime != null and jobStartTime != ''">
 ,j_monitor_start_time 
</if> 
<if test="jobEndTime != null and jobEndTime != ''"> 
,j_monitor_end_time 
</if>
 )
 values
 ( 
<if test="jobId != null and jobId != ''">
 #{jobId} 
</if> 
<if test="jobStartTime != null and jobStartTime != ''">
 ,#{jobStartTime} 
</if> 
<if test="jobEndTime != null and jobEndTime != ''"> 
,#{jobEndTime} 
</if>
 ) 
on conflict(job_id) 
do update <set> 
<if test="jobStartTime != null and jobStartTime != ''"> 
j_monitor_start_time = #{jobStartTime},
 </if> 
<if test="jobEndTime != null and jobEndTime != ''"> 
j_monitor_end_time = #{jobEndTime} 
</if>
 </set> 
</insert>

2、db2数据库

MERGE INTO ${eplatSchema}.TBEDQ01 t1

USING (SELECT #pageEname# AS PAGE_ENAME FROM "SYSIBM".DUAL) t2

ON (t1.PAGE_ENAME=t2.PAGE_ENAME)

WHEN MATCHED THEN

UPDATE SET

PAGE_CNAME=#pageCname#,

REC_REVISOR=#recRevisor#,

REC_REVISE_TIME=#recReviseTime#,

WHEN NOT MATCHED THEN

INSERT

(PAGE_ID, PAGE_ENAME, PAGE_CNAME, )

VALUES

(#pageId#, #pageEname# ,#pageCname#)

3、mysql数据库

INSERT INTO TABLE
(TREE_ID,
TREE_NAME,
TREE_PID
)
VALUES (#treeId#, #treeName#, #treePid#)
ON DUPLICATE KEY
UPDATE
TREE_ID = #treeId#,
TREE_NAME = #treeName#,
TREE_PID = #treePid#

4、oracle数据库

MERGE INTO TABLE t1
USING (SELECT count(*) CO FROM TABLE WHERE TREE_ID = #treeId#) t2
ON (t2.CO <> 0)
WHEN MATCHED THEN
UPDATE SET
TREE_NAME = #treeName#,
WHEN NOT MATCHED THEN
INSERT
(TREE_ID,
TREE_NAME,
TREE_PID
)
VALUES (#treeId#, #treeName#, #treePid#)