从oralce9i开始,oracle 引入了Merge into。 而在Oracle10g中 ,Merge into 可以在update与insert 后添加where以增加额外的条件
具体的语法可以看oracle文档,这里不详细介绍 用法
接下来,介绍MsSQLSERVER中的Mergeinto 。Sqlserver里与Mergeinto的用法基本相同,
只是,语法里并没有where。但是其实也是可以实现同where 的效果的。
具体的语法可以观看官方文档。
[ WITH <common_table_expression> [,...n] ] MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ] USING <table_source> [ [ AS ] table_alias ] ON <merge_search_condition> [ WHEN MATCHED [ AND <clause_search_condition> ] THEN <merge_matched> ] [ ...n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ] THEN <merge_not_matched> ] [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ] THEN <merge_matched> ] [ ...n ] [ <output_clause> ] [ OPTION ( <query_hint> [ ,...n ] ) ] ;
这里插入一段官方说明。可以看到 [ AND <clause_search_condition> ],没错,可以在这里写where后的条件。
实际使用如下:
--材料 insert into student (id,name,score)values(1,'甲',-1); insert into student (id,name,score)values(2,'乙',-1); insert into student (id,name,score)values(3,'丙',-1); insert into score (id,score) values(1,70); insert into score (id,score) values(2,70);
使用的Merge into
merge into student using score on (student.id=score.id) --当然在这里加个 and student.name='甲' 是不会起作用的 when matched and student.name = '甲' then --then 别漏了 update set score= score.score ;
结果跟预想中一致