Mssql merge表达式的使用

 

基本语法格式:

merge into Target_table --目标表

using source_table--源表

on(condition)--匹配条件

when matched then

sql语句

when not matched then

sql语句

when not matched by source then

delete;

when matched子句用于定义当目标表的一个行和来源表的一个行能够匹配时应该采取的操作

create table inventory
(
 part_nointeger integer,
 part_count integer
);
insert into inventory values(1,5);
insert into inventory values(3,6);

create table shipment (
 part_nointeger integer,
 part_count integer
);
insert into shipment values(1,2);
insert into shipment values(2,2);

 

MERGE INTO inventory --目标表
    USING shipment--源表
    ON (inventory.part_nointeger = shipment.part_nointeger)--条件
WHEN MATCHED THEN
   UPDATE SET inventory.part_count = inventory.part_count + shipment.part_count--源表记录存在于目标表中修改目标表记录
WHEN NOT MATCHED THEN
   INSERT VALUES (shipment.part_nointeger,shipment.part_count)--源表记录不存在目标表中向目标表中插入记录

WHEN NOT MATCHED By SOURCE THEN

delete;--注意只在结束时才有分号[当目标表中的某一行在来源表中找不到匹配行时,就删除目标表中的这一行]