Merge Error

Database

Merge Error

I am trying to run a MERGE statement (to perform UPSERT) where one of the column in Primary Index is nullable. I am encountering the following error when I run the same:

"The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s)."

Here is the test case:

create multiset table stg.test87_s

(  col1 integer

  ,col2 integer

  ,col3 integer

  ,col4 integer )

primary index (col1,col2);

insert into stg.test87_s(col1,col2,col3,col4)

       values(1,2,3,null);

insert into stg.test87_s(col1,col2,col3,col4)

       values(2,2,3,null);

insert into stg.test87_s(col1,col2,col3,col4)

       values(3,2,1,null);

insert into stg.test87_s(col1,col2,col3,col4)

       values(4,2,null,null);

create multiset table stg.test87_t

(  col1 integer

  ,col2 integer

  ,col3 integer

  ,col4 integer )

primary index (col1,col2);

insert into stg.test87_t(col1,col2,col3,col4)

       values(1,2,3,null);

insert into stg.test87_t(col1,col2,col3,col4)

       values(2,2,3,null);

insert into stg.test87_t(col1,col2,col3,col4)

       values(3,2,null,null);

alter table stg.test87_t add delete_ind varchar(255);

merge into stg.test87_t t

using stg.test87_s s

on ( syslib.udf_md5(s.col1||s.col2||coalesce(s.col3,0)||coalesce(s.col4,999999))

     = syslib.udf_md5(t.col1||t.col2||coalesce(t.col3,0)||coalesce(t.col4,999999)))

when matched then update set delete_ind='N'

when not matched then

insert(col1,col2,col3,col4,delete_ind) values(s.col1,s.col2,s.col3,s.col4,'N');

1 REPLY
N/A

Re: Merge Error

5758 The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s).

Explanation: The search condition must fully specify an equality constraint on the primary index and partition column(s) between the source and target table. Other set of conditions must be ANDed with primary index and partition column conditions.

You didn't specify the PI of the target table, it's just included in the md5-function, but there's no way to locate that specific row without a Full Table Scan.

You need to specify each column seperately (which is more efficient than casting everything to a string and then hashing it, btw) like

USING test87_s s
ON s.col1 = t.col1
AND s.col2 = t.col2
AND (s.col3 = t.col3 OR (s.col3 IS NULL AND t.col3 IS NULL))
AND (s.col4 = t.col4 OR (s.col4 IS NULL AND t.col4 IS NULL))

Note, there's no way to add the IS NULL logic to the PI columns, but MERGE should be based on the Primary Key of the table anyway.