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
Merge needs to know what uniquely identifies a row in the source and target tables - the logical key. You are trying to say that if you had the values (1, 2, 3, 4, 5) in A and (1, 2, 3, Null, 5) in D, that would be a match. But then (1, 2, 3, 44, 5) would also match (1, 2, 3, Null, 5). Do you want to update the "4" row or the "44" row in table A? That's why merge is complaining aboujt the logic. It might work if you said (A.col4=D.col4 or (A.col4 is null and D.col4 is null)).