title: SQL Conditional Merge When Matched Update when columns do not equal
body: <p>I have a table named SalesOrders. I merge data form multiple tables into it. Within this table, I have a column named Ack which will toggle to 'N' when the row is inserted or updated (I toggle 'Y' in C# code). the trouble I'm having is when I run my query, I only want Ack to change to 'N' when something changes. I tried adding conditions in the WHEN MATCHED statement but the table never updates when there is a change.  </p>
  
  <pre><code>MERGE QA.dbo.SalesOrders AS TARGET USING(SELECT SOD.ORDNUM_28 + 
  LINNUM_28 + DELNUM_28 AS [SalesOrd], SOD.PRTNUM_28, PM.PMDES1_01,                                                                                                                                
  SOD.CURDUE_28, SOD.DUEQTY_28, CPD.CUSTPRT_103, SOM.CUSTPO_27, 
  CPD.UDFREF_103, CPD.PRTNUM_103,SOD.CreationDate, CM.EMAIL1_23, SOD.ORDNUM_28                                                                                                                              
  
  FROM SO_Detail AS SOD FULL OUTER JOIN                                                                                                                               
  Customer_Part_Data AS CPD ON SOD.PRTNUM_28 = CPD.PRTNUM_103 FULL OUTER JOIN                                                                                                                               
  SO_Master AS SOM ON SOD.ORDNUM_28 = SOM.ORDNUM_27 FULL OUTER JOIN                                                                                                                              
  Part_Master AS PM ON SOD.PRTNUM_28 = PM.PRTNUM_01 FULL OUTER JOIN                                                                                                                              
  Customer_Master AS CM ON SOD.CUSTID_28 = CUSTID_23
  
  WHERE (STATUS_28 = '3') AND (SOD.CreationDate &gt; '09 / 14 / 2017') AND 
  (CUSTPO_27 &lt;&gt; ' ') AND (SOM.STYPE_27 = 'CU')                                                                                                                               
  AND (SOD.STK_28 NOT LIKE '%RMA%') AND (SOD.STYPE_28 = 'CU')) SOURCE
  
  ON OrderNum = SOURCE.SalesOrd 
  WHEN MATCHED AND PartNum &lt;&gt; SOURCE.PRTNUM_28 OR Description &lt;&gt; 
  SOURCE.PMDES1_01 OR DueQty &lt;&gt; SOURCE.DUEQTY_28 OR CustPartNum &lt;&gt; 
  SOURCE.CUSTPRT_103 OR CustPo &lt;&gt; SOURCE.CUSTPO_27 OR CustRev &lt;&gt; 
  SOURCE.UDFREF_103 OR ShipDate &lt;&gt;   SOURCE.CURDUE_28 OR email &lt;&gt; 
  SOURCE.EMAIL1_23  // This does not work
  
   THEN
   UPDATE       
   SET  PartNum = SOURCE.PRTNUM_28, Description = 
  SOURCE.PMDES1_01, DueQty = SOURCE.DUEQTY_28, CustPartNum = 
  SOURCE.CUSTPRT_103, CustPo = SOURCE.CUSTPO_27, CustRev =                          
  SOURCE.UDFREF_103, ShipDate = SOURCE.CURDUE_28, email = SOURCE.EMAIL1_23, 
  OrgDate = SOURCE.CreationDate, Ack = 'N' 
  </code></pre>
  
  <p>I noted in the code what does not work (Everything after WHEN MATCHED) - no error just does not update when something is changed. If I remove the code after AND, then everything updates but every time the query is run - thus changing Ack to "N" when nothing really changed. </p>