MERGE only when not null

  • I have a target and a source table where I want to update the target from the source. I have one non-nullable key field, and 3 data fields UDF1,UDF2, and UDF3.

    I get updates provided to me which list the key field and will have one or more of the 3 UDF fields populated. I need to update the main table from these updates.

    I'm pretty new to SQL so I chose the MERGE command to do this.

    My problem is in the code below in where I wrote "PROBLEM HERE". I need to be able to update the fields UDF1,2, or 3 ONLY if the source is not null. I gather there is no opposite to ISNULL() like I have in that line.

    My input may have an update for UDF1, but the input may be NULL for UDF2. I don't want the source UDF2 to get overwritten with NULL. Is that possible?

    MERGE UDFTest --target table

    USING UDFIn --source table

    ON UDFTest.JobNumber = UDFIn.JobNumber

    WHEN MATCHED AND -- if the incomign record is all blanks, then delete original

    (UDFIn.UDF1 is null) and

    (UDFIn.UDF2 is null) and

    (UDFIn.UDF3 is null)

    THEN

    DELETE

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match (if they are not null)

    UPDATE

    SET

    -- PROBLEM HERE

    UDFTest.UDF1 = ISNULL(UDFIn.UDF1,UDFTest.UDF1), -- Isnull(checkexpression, value if true)

    UDFtest.UDF2 = UDFIn.UDF2,

    UDFtest.UDF3 = UDFIn.UDF3

    WHEN NOT MATCHED BY TARGET -- If it's a matched record, AND there is corresponding daa in any UDF field, then insert new record

    AND

    (UDFIn.UDF1 is not null) or

    (UDFIn.UDF2 is not null) or

    (UDFIn.UDF3 is not null)

    THEN

    INSERT (JobNumber, UDF1, UDF2, UDF3)

    VALUES (

    UDFIn.JobNumber,

    UDFIn.UDF1,

    UDFIn.UDF2,

    UDFIn.UDF3

    )

    ;

  • Perhaps UDFtest.UDF2 = COALESCE(UDFIn.UDF2,UDFtest.UDF2) ? If UDFIn.UDF2 is null then it will use the original value of your UDFtest

  • Yes, I was not familiar with that function. That should do it. Thank you.

  • You do realize that for most purposes ISNULL and COALESCE are the same. I'm confused why you think that ISNULL won't work, but COALESCE will.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (11/1/2016)


    You do realize that for most purposes ISNULL and COALESCE are the same. I'm confused why you think that ISNULL won't work, but COALESCE will.

    Drew

    I figured it was more control of order of comparision. But, maybe it won't, haven't tried it yet because... I just figured out any function will work in that syntax so iif(...) will work, too. For my purposes I think I can make that work better for my needs anyway.

  • shorton2 (11/1/2016)


    drew.allen (11/1/2016)


    You do realize that for most purposes ISNULL and COALESCE are the same. I'm confused why you think that ISNULL won't work, but COALESCE will.

    Drew

    I figured it was more control of order of comparision. But, maybe it won't, haven't tried it yet because... I just figured out any function will work in that syntax so iif(...) will work, too. For my purposes I think I can make that work better for my needs anyway.

    No, they both work from left to right. The most obvious difference is that ISNULL takes two parameters whereas COALESCE takes a variable number of parameters. All of the differences are located at COALESCE under the section "Comparing COALESCE and ISNULL".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Probably a better way but this seems to do what I want. Allows the users to feed me an "x" to explicitly clear a field, and if they do nothing (feed me a NULL), it leaves any existing entry alone:

    ...

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match, NULL if 'X'

    UPDATE

    SET

    UDFTest.UDF1 = iif( (UPPER(RTRIM(UDFIn.UDF1)) ='X'), NULL, IIF((UDFIn.UDF1 IS NOT NULL), UDFIn.UDF1, UDFTest.UDF1)),

    UDFTest.UDF2 = iif( (UPPER(RTRIM(UDFIn.UDF2)) ='X'), NULL, IIF((UDFIn.UDF2 IS NOT NULL), UDFIn.UDF2, UDFTest.UDF2)),

    UDFTest.UDF3 = iif( (UPPER(RTRIM(UDFIn.UDF3)) ='X'), NULL, IIF((UDFIn.UDF3 IS NOT NULL), UDFIn.UDF3, UDFTest.UDF3))

    ...

  • shorton2 (11/1/2016)


    Probably a better way but this seems to do what I want. Allows the users to feed me an "x" to explicitly clear a field, and if they do nothing (feed me a NULL), it leaves any existing entry alone:

    ...

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match, NULL if 'X'

    UPDATE

    SET

    UDFTest.UDF1 = iif( (UPPER(RTRIM(UDFIn.UDF1)) ='X'), NULL, IIF((UDFIn.UDF1 IS NOT NULL), UDFIn.UDF1, UDFTest.UDF1)),

    UDFTest.UDF2 = iif( (UPPER(RTRIM(UDFIn.UDF2)) ='X'), NULL, IIF((UDFIn.UDF2 IS NOT NULL), UDFIn.UDF2, UDFTest.UDF2)),

    UDFTest.UDF3 = iif( (UPPER(RTRIM(UDFIn.UDF3)) ='X'), NULL, IIF((UDFIn.UDF3 IS NOT NULL), UDFIn.UDF3, UDFTest.UDF3))

    ...

    This should give you the same results

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match, NULL if 'X'

    UPDATE

    SET

    -- Unless you are using a case-sensitive collation, there is no need for the UPPER.

    UDFTest.UDF1 = CASE WHEN UDFIn.UDF1 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF1, UDFTest.UDF1) END,

    UDFTest.UDF2 = CASE WHEN UDFIn.UDF2 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF2, UDFTest.UDF2) END,

    UDFTest.UDF3 = CASE WHEN UDFIn.UDF3 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF3, UDFTest.UDF3) END

  • Thank you . Much cleaner.

    I was all happy getting that part to work but missed a input case. When the user gives me a record that does not match that has one or more 'x' value fields, they get added, as 'x' which I don't want.

    MERGE UDFTest --target table

    USING UDFIn --source table

    ON UDFTest.JobNumber = UDFIn.JobNumber

    WHEN MATCHED AND -- if the incomign record is all X's, then delete original record completely

    (UPPER(RTRIM(UDFIn.UDF1)) = 'X') and

    (UPPER(RTRIM(UDFIn.UDF2)) = 'X') and

    (UPPER(RTRIM(UDFIn.UDF3)) = 'X')

    THEN

    DELETE

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match, NULL if 'X', leave alone if update field is NULL

    UPDATE

    SET

    UDFTest.UDF1 = CASE WHEN UDFIn.UDF1 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF1, UDFTest.UDF1) END,

    UDFTest.UDF2 = CASE WHEN UDFIn.UDF2 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF2, UDFTest.UDF2) END,

    UDFTest.UDF3 = CASE WHEN UDFIn.UDF3 ='X' THEN NULL ELSE ISNULL(UDFIn.UDF3, UDFTest.UDF3) END

    WHEN NOT MATCHED BY TARGET -- If it's a matched record, AND there is corresponding data in any UDF field, then insert new record

    AND

    (UDFIn.UDF1 is not null) or

    (UDFIn.UDF2 is not null) or

    (UDFIn.UDF3 is not null)

    THEN

    INSERT (JobNumber, UDF1, UDF2, UDF3)

    VALUES (

    UDFIn.JobNumber,

    UDFIn.UDF1,

    UDFIn.UDF2,

    UDFIn.UDF3

    )

    WHEN NOT MATCHED BY SOURCE

    AND

    (UDFTest.UDF1 is null) and

    (UDFTest.UDF2 is null) and

    (UDFTest.UDF3 is null)

    THEN

    DELETE

    ;

    SELECT * FROM UDFTest

    order by JobNumber;

  • Deleted: Solution would not work

  • (Nice.) --> scratch that part. Looked good anyway 🙂

    Any easy way to fix my 'NOT MATCHED' 'x' input issue?

    What I need is if there is an 'x' value on the unmatched input that it adds that record with a NULL instead of the 'x'. I could end up with a target record with all nulls in the 3 UDF fields but that's not a problem.

    I see how I could exclude a source record if all 3 were 'x's but don't see how I can handle them individually. That is if I have an unmatched input like this:

    JobNumber UDF1 UDF2 UDF3

    1234556 dog, x, x

  • shorton2 (11/1/2016)


    Probably a better way but this seems to do what I want. Allows the users to feed me an "x" to explicitly clear a field, and if they do nothing (feed me a NULL), it leaves any existing entry alone:

    Another option

    WHEN MATCHED THEN -- if the incoming record is a match, then udpate the fields to match, NULL if 'X'

    UPDATE

    SET

    UDFTest.UDF1 = NULLIF(ISNULL(UDFIn.UDF1, UDFTest.UDF1), 'X'),

    UDFTest.UDF2 = NULLIF(ISNULL(UDFIn.UDF2, UDFTest.UDF2), 'X'),

    UDFTest.UDF3 = NULLIF(ISNULL(UDFIn.UDF3, UDFTest.UDF3), 'X')

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply