title: Multiple Update Statement in SQL Server MERGE
body: <p>SQL Server Version : Microsoft SQL Server 2012 - 11.0.2218.0 (x64)</p>
  
  <p>I got the following exception when I ran this Query.
  Exception : <strong>An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.</strong></p>
  
  <p>I know that the exception was for more than once Update statement in Merge Statement.
  would you please suggest me that <strong>how I achieve the following SQL Query logic?</strong></p>
  
  <p><strong><em>Based on One column,<br>
                 When Matched and column is not null then update only one different column.<br>
                 When Matched and column is null then update the most of the columns.<br>
                 When Not Matched then Insert.</em></strong></p>
  
  <p>The Complete SQL is </p>
  
  <pre><code>MERGE TargetTable AS targetT 
  USING   SourceTable AS sourceT ON sourceT.Npi = targetT.Npi
  WHEN    MATCHED AND IsNull(targetT.SPI, '') &lt;&gt; '' 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);
  </code></pre>