526互联

MssqlServer与Oracle里的Merge into 里的and与where

发布时间 2023-12-15 00:33:57作者: mikodopants

从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 ;

结果跟预想中一致