November 1, 2016 at 9:29 am
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
)
;
November 1, 2016 at 9:34 am
Perhaps UDFtest.UDF2 = COALESCE(UDFIn.UDF2,UDFtest.UDF2) ? If UDFIn.UDF2 is null then it will use the original value of your UDFtest
November 1, 2016 at 9:38 am
Yes, I was not familiar with that function. That should do it. Thank you.
November 1, 2016 at 9:58 am
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
November 1, 2016 at 10:19 am
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.
November 1, 2016 at 12:45 pm
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
November 1, 2016 at 1:03 pm
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))
...
November 1, 2016 at 1:34 pm
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
November 1, 2016 at 1:42 pm
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;
November 1, 2016 at 1:43 pm
Deleted: Solution would not work
November 1, 2016 at 1:52 pm
(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
November 1, 2016 at 1:55 pm
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