SQL Conditional Merge When Matched Update when columns do not equal

Question

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.

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 > '09 / 14 / 2017') AND 
(CUSTPO_27 <> ' ') 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 <> SOURCE.PRTNUM_28 OR Description <> 
SOURCE.PMDES1_01 OR DueQty <> SOURCE.DUEQTY_28 OR CustPartNum <> 
SOURCE.CUSTPRT_103 OR CustPo <> SOURCE.CUSTPO_27 OR CustRev <> 
SOURCE.UDFREF_103 OR ShipDate <>   SOURCE.CURDUE_28 OR email <> 
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' 

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.

Answer 1

Figured it out: My issue was, I am a victim of not understanding NULL. There is good discussion found here: Why does NULL = NULL evaluate to false in SQL server. Basically, the conditions could not fire the update because they simply did not know the answer (NULL). I needed to update my INSERT to include a CASE so if the value is NULL - insert ' ' (a Blank) so my conditions would have something to compare.

Answer 2

With some basic formatting the error became pretty obvious. You were missing a parenthesis in your predicates when matched. Notice how when this isn't a wall of sql you can actually see what is going on.

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 > '09 / 14 / 2017' --Is this a date column? If so you need to use the ANSI standard YYYYmmdd
        AND CUSTPO_27 <> ' '
        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 
( --you need this here
    PartNum <> SOURCE.PRTNUM_28 
    OR Description <> SOURCE.PMDES1_01 
    OR DueQty <> SOURCE.DUEQTY_28 
    OR CustPartNum <> SOURCE.CUSTPRT_103 
    OR CustPo <> SOURCE.CUSTPO_27 
    OR CustRev <> SOURCE.UDFREF_103 
    OR ShipDate <> SOURCE.CURDUE_28 
    OR email <> SOURCE.EMAIL1_23  --// This does not work
) --Without the parenthesis the update would fire when ANY of those conditions are met
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'