SQL Server MERGE 中的多个更新语句

2022-01-17 00:00:00 merge sql-update sql sql-server sql-server-2012

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?

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

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

推荐答案

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

根据 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>

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

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

如果指定了两个子句，则第一个子句必须伴随一个 AND <search_condition> 子句.对于任何给定的行，第二个 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 在 <merge_matched>子句，并且超过<table_source> 的一行匹配 target_table 中的一行，基于<merge_search_condition>，SQL Server 返回错误.

If UPDATE is specified in the <merge_matched> clause, and more than one 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

希望这会有所帮助.

相关文章