Need Help in a issue using merge in update/insert on table.

Database

Need Help in a issue using merge in update/insert on table.

We had a table A and we are using merge into A using(select * from b join c) D

on A.col1=D.col1

and A.col2=D.col2

and A.col3=D.col3

and A.col4=D.col4

and A.col5=D.col5

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

 

on A.col1=D.col1

and A.col2=D.col2

and A.col3=D.col3

and (A.col4=D.col4 or (A.col4 is null) or (D.col4 is null))

and A.col5=D.col5

 

is giving me error as : target row updated by multiple source rows.

 

Please help.

 

This is the first time I am using Merge not sure if what I am doing is correct

 

1 REPLY
Teradata Employee

Re: Need Help in a issue using merge in update/insert on table.

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)).