Merge - Update Column Values Separately, Based On Logic In When Matched Block

Earlier today, I asked this question and got the answer I was looking for. Now I have a follow-up question: What I want: I want the MERGE to compare each column value, per row, in

Solution 1:

Not having your data, and not wanting to re-type your query from an image, I created a sample that I think demonstrates what you want:

createtable t (ID intnotnull,Col1 intnull,Col2 intnull)
createtable s (ID intnotnull,Col1 intnull,Col2 intnull)

insertinto t(ID,Col1,Col2) values (1,1,null),(2,null,2)
insertinto s(ID,Col1,Col2) values (1,3,4),(2,5,6),(3,7,8)

;mergeinto t
using s
on t.ID = s.ID
whennot matched theninsert (ID,Col1,Col2) values (s.ID,s.Col1,s.Col2)
when matched thenupdateset Col1 =COALESCE(t.Col1,s.Col1),
Col2 =COALESCE(t.Col2,s.Col2)
;
select*from t

Result:

ID          Col1        Col2
----------- ----------- -----------
1           1           4
2           5           2
3           7           8

Where the key is to use COALESCE to avoid updating a column value if it already has one (which I think is what you're trying to achieve)

Solution 2:

I'm not sure I understand the question - do you mean... well, I'm not sure what you mean. Minus the extra trailing OR, you have two conditions. If either (or both) of these evaluate to TRUE, the target table will be updated by the THEN UPDATE

However, you are MATCHing on unique_key, and the first condition (s.unique_key IS NOT NULL AND t.unique_key IS NULL) will never be true, because if it were true then the records would not be matched. So the first part of the OR can be ignored.

Also, since the records are MATCHED on unique_key, it is completely redundant to update the target with the source value of unique_key - they are already the same.

Thus, as it is currently written, your MERGE is:

MERGE dbo.input311 AS T
USING dbo.input311staging AS S
ON S.unique_key = S.unique_key
WHENNOT MATCHED BY TARGET THEN
   INSERT
     -- insert statement I'm too lazy to typeWHEN MATCHED AND s.created_date ISNOT NULL AND t.created_date IS NULL THEN
   UPDATE SET t.created_date = s.created_date