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

Database

Urgent :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

 

3 REPLIES
N/A

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

You probaly have to use and (A.col4=D.col4 or ((A.col4 is null) AND (D.col4 is null)))

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

I also tried by using (A.col4=D.col4 or (A.col4 is null)) even then getting the same error
N/A

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

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.