1、需求:找数据表objectTable 表中id为123456,没有就插入新记录,有就进行更新。
MERGE INTO objectTable t
USING ( select '123456' ID,'张三' Name ) s
ON (s.ID =t.ID )
WHEN MATCHED THEN
UPDATE SET t.Name =s.Name
WHEN NOT MATCHED THEN
INSERT (ID, Name) VALUES (s.ID, s.Name);
2、目标表objectTable ,源表sourceTable ,对目标表中id相同的进行同步更新和插入。
MERGE INTO objectTable t
USING (select 35 id from sourceTable) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.note =s.note--.....
WHEN NOT MATCHED THEN
INSERT (id, note) VALUES (s.id,s.note);