Update columns based on different criteria using merge statement

Question

So I am using the following merge statement to do an update and insert in one of my tables, however I would like to update another column in the Target table in addition to the one already in the statement. I'm just not sure if I can do that or how to go about it. What I want to do is add in something like

when MATCHED and Source.EventType='Change' Then UPDATE SET Target.DEDCAMT_1 = Source.EmpDedAmt

Can I do that in the same Merge statement?

    MERGE dbo.BENTBL as Target
    using dbo.BenPaycdUpdate as Source
    ON Source.EmpID = Target.EMPLOYID AND Source.Paycode = Target.DEDUCTON
    WHEN MATCHED AND source.EventType='Stop'
    THEN UPDATE
    SET INACTIVE = '1',
    Target.DEDENDDT = Source.AsOfDate
    WHEN NOT MATCHED BY Target
    AND Source.PayCode <> 'FSAH' 
    THEN INSERT (EMPLOYID, DEDUCTON, INACTIVE, DEDBEGDT, DEDCAMNT_1)
    VALUES (Source.EmpID, Source.Paycode,'0', Source.AsofDate,
    CAST(REPLACE  (REPLACE(ISNULL(source.EmpDed,0),',',''),'$','') AS numeric(19,5)));

score 0 · Accepted Answer · answered Dec 19 '18 at 05:43

0

You can move the condition from the merge to a case expression, like this:

MERGE dbo.BENTBL as Target
using dbo.BenPaycdUpdate as Source
ON Source.EmpID = Target.EMPLOYID AND Source.Paycode = Target.DEDUCTON
WHEN MATCHED THEN 
UPDATE
SET INACTIVE = CASE WHEN source.EventType = 'Stop' THEN '1' ELSE INACTIVE END,
    Target.DEDCAMT_1 = CASE WHEN source.EventType = 'Change' THEN Source.EmpDedAmt ELSE Target.DEDCAMT_1 END,
    Target.DEDENDDT = Source.AsOfDate
WHEN NOT MATCHED BY Target AND Source.PayCode <> 'FSAH' THEN 
INSERT (EMPLOYID, DEDUCTON, INACTIVE, DEDBEGDT, DEDCAMNT_1)
VALUES (Source.EmpID, Source.Paycode,'0', Source.AsofDate,
CAST(REPLACE  (REPLACE(ISNULL(source.EmpDed,0),',',''),'$','') AS numeric(19,5)));

answered Dec 19 '18 at 05:43

Zohar Peled

79,642
10
69
121

Thank you @Zohar! This is exactly what I was trying to do I just couldn't figure out how to implement it – KAL077 Dec 20 '18 at 14:28
Glad to help :-) – Zohar Peled Dec 20 '18 at 15:54
I wonder if maybe you can help me with an issue I'm running into. I was testing this merge statement using `when matched AND target.EMPLOYID='11111' THEN update` and `when not matched AND source.EmpID='1111' THEN insert` These were id's that I knew for sure were going trigger an update or insert. When I remove the id's though and run without a filter on the ID it errors out with an error converting nvarchar to numeric. Do you know what might cause this? – KAL077 Dec 20 '18 at 21:06
1

This is usually a result of an implicit conversion triggered by an attempt to compare different data types, or to assign the wrong data type to a variable or column. – Zohar Peled Dec 20 '18 at 21:34
but why would it work with a specific id and not otherwise? The only column that I had to do a conversion on was the source.EmpDed because in the table that column is datatype nvarchar and in the target table that column is numeric. I'm thinking that's where the error is stemming from. There's 93 records in my source table but I still don't understand why it would work with a specific id and not others if they all have a value in that column. – KAL077 Dec 20 '18 at 21:53
1

because in at least one of the records with different ids there is a value in that column that can't be converted to numeric. You can select from the source table using `try_cast` on that column is see what record(s) the `try_cast` returns null on. These are the records that's failing. – Zohar Peled Dec 20 '18 at 21:59
Okay so I was being dumb. I didn't do the cast here `Target.DEDCAMT_1 = CASE WHEN source.EventType = 'Change' THEN Source.EmpDedAmt ` so of course like you said it was trying to assign the wrong data type to the column. Thanks so much for your time and continued assistance – KAL077 Dec 20 '18 at 22:43
Again, Glad to help :-) – Zohar Peled Dec 21 '18 at 04:38

Update columns based on different criteria using merge statement

1 Answers1