We had a table A and we are using merge into A using(select * from b join c) D
when matched update A
ehen unmatched insert in to A
but my problem here is A.col4 and D.col4 is having nulls and as null is not equal to null the condition is failing and records are getting inserted but even in this case I want the records to go to update.
but modifying the condition as below
and (A.col4=D.col4 or (A.col4 is null) or (D.col4 is null))
is giving me error as : target row updated by multiple source rows.
This is the first time I am using Merge not sure if what I am doing is correct
Did you try my ANDed condition?
If you still get that multiple update error you got multiple rows in your source with NULL.
Then you must add logic in your Select to get rid of those, either by applying an aggregate or a ROW_NUMBER.