Multiple Update Statement in SQL Server MERGE [duplicate]

Question

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

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.

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?

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.

The Complete SQL is

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

Please note this did not answer OP question, it was just an elaboration on the MERGE clause.

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>

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.

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.

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.

Source: MSDN

Hope this helps.

Answer 2

Dont consider this as an answer, I did not test the code either..What I tried, is to add a CASE statement for every column.

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);

I have added update for only two columns. This will be lengthy, but must ideally work.