SQL Server MERGE中的多个更新语句 [英] Multiple Update Statement in SQL Server MERGE

问题描述

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

SQL Server Version : Microsoft SQL Server 2012 - 11.0.2218.0 (x64)

运行此查询时，出现以下异常. 例外:类型为'WHEN MATCHED'的操作不能在MERGE语句的'UPDATE'子句中出现多次.

I got the following exception when I ran this Query. Exception : An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

我知道该异常多次出现在Merge语句中的Update语句中. 您能否建议我我如何实现以下SQL查询逻辑?

I know that the exception was for more than once Update statement in Merge Statement. would you please suggest me that how I achieve the following SQL Query logic?

基于一列，
当Matched和column不为null时，则仅更新一个不同的列.
当Matched和column为null时，则更新大多数列.
如果不匹配，则插入.

Based on One column,
When Matched and column is not null then update only one different column.
When Matched and column is null then update the most of the columns.
When Not Matched then Insert.

完整的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);

推荐答案

按照 MSDN ，如果有两个WHEN MATCHED子句，则必须指定一个UPDATE动作，而一个必须指定一个DELETE动作."

As per MSDN, "If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action".

WHEN MATCHED THEN <merge_matched>

指定根据该子句更新或删除与ON <merge_search_condition>返回的行匹配并满足任何其他搜索条件的target_table的所有行. MERGE语句最多可以包含两个WHEN MATCHED子句.

Specifies that all rows of target_table that match the rows returned by ON <merge_search_condition>, and satisfy any additional search condition, are either updated or deleted according to the clause. The MERGE statement can have at most two WHEN MATCHED clauses.

如果指定了两个子句，则第一个子句必须带有一个AND <search_condition>子句.对于任何给定的行，仅当第一个WHEN MATCHED子句不适用时，才应用第二个WHEN MATCHED子句.如果有两个WHEN MATCHED子句，则必须指定一个UPDATE动作，而一个必须指定DELETE动作.

If two clauses are specified, then the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first is not. If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

如果子句中指定了UPDATE，且大于 一行匹配基于以下内容的target_table中的一行 ，SQL Server返回错误.

If UPDATE is specified in the clause, and more than one row of matches a row in target_table based on , SQL Server returns an error.

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

来源: MSDN

希望这会有所帮助.

这篇关于SQL Server MERGE中的多个更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！