MERGE语句根据与另一个表匹配的值更新表中的数据

转载自SQL Server Merge语句 - SQL Server教程™ (yiibai.com)

一、merge语句详解

1、假设有两个表名为：source表和target表，并且需要根据source表中匹配的值更新target表。 有三种情况：

• source表有一些target表中不存在的行。在这种情况下，需要将source表中的行插入到target表中。
• target表有一些source表中不存在的行。 在这种情况下，需要从target表中删除行。
• source表的某些行具有与target表中的行相同的键。 但是，这些行在非键列中具有不同的值。 在这种情况下，需要使用来自source表的值更新target表中的行

2、下图说明了source表和target表以及相应的操作：插入，更新和删除：

3、如果单独使用INSERT，UPDATE和DELETE语句，则必须构造三个单独的语句，以使用source表中的匹配行将数据更新到target表。

但是，SQL Server提供MERGE语句以用于同时执行三个操作。 以下是MERGE语句的语法：

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

首先，在MERGE子句中指定source_table表和target_table表。

其次，merge_condition确定source_table表中的行如何与target_table表中的行匹配。 它类似于join子句中的join条件。 通常，使用主键或唯一键的键列进行匹配。

第三，merge_condition有三种状态：MATCHED，NOT MATCHED和NOT MATCHED BY SOURCE。

• MATCHED：这些是与合并条件匹配的行。 在图中，它们显示为蓝色。 对于匹配的行，需要使用source_table表中的值更新target_table表中的行列。
• NOT MATCHED：这些是source_table表中的行，target_table表中没有任何匹配的行。 在图中，它们显示为橙色。 在这种情况下，需要将source_table表中的行添加到target_table表。 请注意，NOT MATCHED BY TARGET也称为目标不匹配。
• NO MATCHED BY SOURCE：这些是target_table表中与source_table表中的任何行都不匹配的行。 它们在图中显示为绿色。 如果要将target_table表与source_table表中的数据同步，则需要使用此匹配条件从target_table表中删除行。

二、SQL Server MERGE语句示例

假设有两个表：sales.category和sales.category_staging，它们按产品类别存储销售额。参考以下创建语句：

CREATE TABLE sales.category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);

INSERT INTO sales.category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (2,'Comfort Bicycles',25000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',10000);


CREATE TABLE sales.category_staging (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);


INSERT INTO sales.category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',20000),
    (5,'Electric Bikes',10000),
    (6,'Mountain Bikes',10000);

要使用sales.category_staging(源表)中的值将数据更新到sales.category(目标表)，请使用以下MERGE语句：

MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;