sql - SQL Server MERGE 中的多个更新语句

Question

这个问题已经有答案了:

Why WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement? (2 个回答)

已关闭 3 年前。

SQL Server 版本:Microsoft SQL Server 2012 - 11.0.2218.0 (x64)

当我运行此查询时，出现以下异常。异常(exception):“WHEN MATCHED”类型的操作不能在 MERGE 语句的“UPDATE”子句中出现多次。

我知道异常是在合并语句中不止一次更新语句。您能否建议我如何实现以下 SQL 查询逻辑？

基于一栏，
当匹配并且列不为空时，则仅更新一个不同的列。
当匹配并且列为空时，则更新大部分列。
当不匹配时插入。

完整的SQL是

MERGE TargetTable AS targetT 
USING   SourceTable AS sourceT ON sourceT.Npi = targetT.Npi
WHEN    MATCHED AND IsNull(targetT.SPI, '') <> '' THEN
        UPDATE SET targetT.Taxonomy = sourceT.Taxonomy --Update Only One Column

WHEN    MATCHED AND IsNull(targetT.SPI,'')= '' THEN --Update Rest of the Columns
        UPDATE SET targetT.state_license_no = sourceT.state_license_no, targetT.NPI = sourceT.NPI, targetT.PrefixName = sourceT.PrefixName,targetT.last_name = sourceT.last_name,targetT.first_name = sourceT.first_name
               ,MiddleName = sourceT.MiddleName,targetT.SuffixName = sourceT.SuffixName, targetT.address_1 = sourceT.address_1,targetT.address_2 = sourceT.address_2,targetT.City = sourceT.City,targetT.State = sourceT.State
               ,zip = sourceT.zip,targetT.phone = sourceT.phone,targetT.Fax = sourceT.Fax,targetT.last_modified_date = sourceT.last_modified_date,targetT.Taxonomy = sourceT.Taxonomy           

WHEN    NOT MATCHED BY TARGET --Insert New Row
        THEN
        INSERT (state_license_no, NPI, prefixname, last_name, first_name, MiddleName, SuffixName, address_1, address_2, City, State, zip, phone, Fax, last_modified_date, Taxonomy, Data_source)
                                    VALUES (sourceT.state_license_no, sourceT.NPI, sourceT.PrefixName, sourceT.last_name, sourceT.first_name, sourceT.MiddleName, sourceT.SuffixName, 
                                    sourceT.address_1, sourceT.address_2, sourceT.City, sourceT.State, sourceT.zip, 
                                    sourceT.phone, sourceT.Fax, sourceT.last_modified_date, sourceT.Taxonomy, sourceT.Data_source);

Answer 1

请注意，这并没有回答OP问题，它只是对MERGE子句的详细阐述。

根据MSDN ，“如果有两个 WHEN MATCHED 子句，则一个必须指定一个 UPDATE 操作，一个必须指定一个 DELETE 操作”。

WHEN MATCHED THEN <merge_matched>

指定 target_table 中与 ON <merge_search_condition> 返回的行匹配的所有行，并满足任何附加搜索条件，根据 子句更新或删除。 MERGE语句最多可以有两个 WHEN MATCHED条款。

如果指定了两个子句，则第一个子句必须带有 AND <search_condition>条款。对于任何给定行，第二个 WHEN MATCHED仅当第一个子句不适用时才适用。如果有两个WHEN MATCHED子句，那么必须指定 UPDATE操作，并且必须指定 DELETE 操作。

If UPDATE is specified in the <merge_matched> clause, and more thanone row of <table_source>matches a row in target_table based on<merge_search_condition>, SQL Server returns an error.

The MERGE statement cannot update the same row more than once, or update and delete the same row.

来源:MSDN

希望这有帮助。