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.

You are going to have to do better than "it doesn't work". What does that mean? That statement is like dropping off your car at the mechanic with a note saying "my car doesn't work, please fix it". — Sean Lange, Sep 26 '17 at 21:08

score 1 · Answer 1 · answered Sep 26 '17 at 21:15

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'

thanks for the fromatting tip - I try to keep formatting better but I'm doing this in Visual Studio and it changes the formatting back to "the wall". I added the parenthesis as you recommended and the update still does not fire. Just to clarify, when I have the conditions in the statement, the update never fires - if i don't have the conditions in, it always fires. I only want it to fire and change the column Ack to "N" if one of the conditions are met. I know for certain that one of the rows should change because I introduce a difference. Thanks for your attention to this — Alan Robertson, Sep 27 '17 at 12:13
I would suggest moving your sql logic to a stored procedure. Then you can make changes to it without deploying your code. And you don't have to deal with horrific formatting. — Sean Lange, Sep 27 '17 at 13:10
As for the issue at hand I can't possibly help because I have no tables to work with. My guess is that you are not getting a match on OrderNum and SalesOrd but that is a shot in the dark. — Sean Lange, Sep 27 '17 at 13:12
Thanks - I narrowed it down. It turns out that the condition does not work on CustRev <> SOURCE.UDFREF_103 (only - the rest of the conditions work). I know the column names are correct as I copied and pasted them from part of the code that works. I will change to a stored procedure - good tip. — Alan Robertson, Sep 27 '17 at 13:37

score 1 · Accepted Answer · answered Sep 27 '17 at 18:55

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.

SQL Conditional Merge When Matched Update when columns do not equal

2 Answers2