Geek Answers Handbook

SQL Server MERGE Multiple Upgrade Statement

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

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

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

Based on a single column,
             When "Consistent" and "Column" is not zero, update only one column.
             When Consistent and Column is null, update most of the columns.
             When not agreed, then insert.

Full 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);
+4
source share
3 answers

According to MSDN , "If there are two sentences WHEN MATCHED, then you must specify an action UPDATE, and you must specify an action DELETE."

WHEN MATCHED THEN <merge_matched>

, target_table, , ON <merge_search_condition>, , . MERGE WHEN MATCHED.

, AND <search_condition>. WHEN MATCHED , . WHEN MATCHED, UPDATE, DELETE.

UPDATE , target_table , SQL Server .

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

: MSDN

, .

+5

, . , CASE .

MERGE TargetTable AS targetT 
USING   SourceTable AS sourceT ON sourceT.Npi = targetT.Npi
WHEN    MATCHED THEN
        UPDATE  SET targetT.Taxonomy = CASE WHEN  IsNull(targetT.SPI, '') <> '' THEN sourceT.Taxonomy ELSE targetT.Taxonomy END,
                targetT.state_license_no = CASE WHEN IsNull(targetT.SPI,'')= '' THEN sourceT.state_license_no ELSE targetT.state_license_no END
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);

. , .

+2

, " " .

target_table - INSTEAD OF UPDATE INSTEAD OF DELETE, . .

i.e " ..." (update) else (). .

, " "

0

More articles:


All Articles