merge into 插入或更新数据

来源：互联网发布：程序员必读书籍编辑：程序博客网时间：2024/05/20 00:39

1.Oracle中Merge into能整合update和insert两个语句

2.这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE

语法:

MERGE [INTO] [schema.]table [alias]
USING {[schema.]table|views|query} [alias]
ON {condition}
WHEN MATCHED THEN UPDATE SET {clause}
WHEN NOT MATCHED THEN INSERT VALUES {clause}

例子:

重复数据不插入+不重复则插入 :
merge into haode h using(select '文迪1' as name from dual) b on(h.name=b.name) when not matched then insert values(7,'文迪1','好的6');

-->0行合并...

重复数据更新+不重复则插入:

merge into haode h using(select '文迪1' as name,'好的merge' as character from dual) b on(h.name=b.name) when matched then update set h.character=b.character when not matched then insert values(7,'文迪7','merge');

-->1行合并...

参考http://www.cnblogs.com/jiangguang/archive/2012/12/24/2830581.html

0 0