Merge into 详细介绍
MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。
通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,
连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
语法:
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]
例如:学生表中如果存在名字为zhangsan的人,那么把年龄更新成23,如果不存在,则插入。
merge into student st
using (select "zhangsan" name from dual) tb on st.name = tb.name
when mathed then
update set st.age = 23
when not matched then
insert (st.name, st.age) values ("zhangsan", 23)
commit