Using MERGE into a table with an identity column. Result: Failure 5758

Database

Using MERGE into a table with an identity column. Result: Failure 5758

Hello all,

I would like to point out what I think is a bug and solicit ideas for a way to make this work.

I have a stage table and I want to merge it into a table with a surrogate key.  I want auto-generate the surrogate key on insert and I only want to update records that need updating.

At this stage I'm going to punt and change my code to two statements: Update and Insert.  It leaves me wondering what the MERGE statement is for.  It likes to update every record, even if the fields are identical, and it doesn't work with an identity column.  Is this because the MERGE statement is new and the kinks are still being worked out?

Here is an example I whipped up that shows what I'm running into.

Thans in advance.

Matt

/*
Our goal is to take a stage table (T_T1) with no SKEY
and merge it into a table (T_T2) with an SKEY.
We are setting the SKEY by making it an identity column.

*/

--Create T1, the source table.
CREATE SET TABLE T_T1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
NKEY VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
STRING1 VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
DATE1 DATE FORMAT 'yyyy-mm-dd')
UNIQUE PRIMARY INDEX ( NKEY );

--insert two records (one for to be updated, the other to be inserted.
insert into T_T1 (NKEY, STRING1, DATE1) values ('REC1','This is an update.','2012-09-24');
insert into T_T1 (NKEY, STRING1, DATE1) values ('REC2','This is inserted.','2012-09-24');

--Create T2, the destination table
CREATE SET TABLE T_T2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
SKEY INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 10
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000000000
NO CYCLE),
NKEY VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
STRING1 VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
DATE1 DATE FORMAT 'yyyy-mm-dd')
UNIQUE PRIMARY INDEX ( SKEY );

--insert the T2 record to be updated.
insert into T_T2 (NKEY, STRING1, DATE1) values ('REC1','This gets overwritten.','2012-09-24');

--Run the merge
MERGE INTO T_T2 T2
USING (
select coalesce(T_T2.SKEY,-1), T_T1.NKEY, T_T1.STRING1, T_T1.DATE1
from T_T1
LEFT
JOIN T_T2 on T_T2.NKEY = T_T1.NKEY
--Subtract existing contents of T_T2 so we only pick up differences
--We don't want to update every record that matches.
minus
select SKEY, NKEY, STRING1, DATE1 from T_T2
) T1
ON T2.SKEY = T1.SKEY
WHEN MATCHED THEN UPDATE
SET STRING1 = T1.STRING1,
DATE1 = T1.DATE1
WHEN NOT MATCHED THEN INSERT (
SKEY,
NKEY,
STRING1,
DATE1
)
values (
NULL --Let the identity column pick an SKEY,
T1.NKEY,
T1.STRING1,
T1.DATE1
);

/*
The above statement errors with:
*** Failure 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).

If you remove the WHEN NOT MATCHED, it works fine, so it's just the insert causing the
issue, however I can't see why there is an issue.
*/

Tags (3)
5 REPLIES

Re: Using MERGE into a table with an identity column. Result: Failure 5758

Matt:

This is a common misundertanding of the clause 'DEFAULT' (even in the other RDBMS's out there).

DEFAULT is used when no value is provided, not when value NULL is provided (as you do in your example).

Try:

WHEN   NOT MATCHED THEN INSERT (

       NKEY,

       STRING1,

       DATE1

       )

values (

       T1.NKEY,

       T1.STRING1,

       T1.DATE1

       );

 

HTH.

 

Cheers.

 

Carlos.

Re: Using MERGE into a table with an identity column. Result: Failure 5758

CarlasAL- I've tried with your idea(removed the SKEY column).

But it's not working, telling same issue.

Teradata Employee

Re: Using MERGE into a table with an identity column. Result: Failure 5758

The MINUS operation is a creaative way to eliminate rows that have not changed. However, it breaks the explicit rule for the USING subquery that it must have an explicit equality condition on fields known to be unique in the queried table.

To accomplish what you are trying to do, you will have to eliminate those records with a set of conditions in the where clause of your join.

N/A

Re: Using MERGE into a table with an identity column. Result: Failure 5758

I eliminated the subquery to test and got:

MERGE Failed.  [5977] Invalid MERGE-INTO statement: Target table with Identity column as primary index is not allowed with WHEN NOT MATCHED specifiction

Re: Using MERGE into a table with an identity column. Result: Failure 5758

Actually your question gave me the answer, when I was looking for the reason through the forum for this same issue.

1stly, any PI, UPI or PARTITION column must be there in SELECT clause of the source query.

2ndly, in the 'WHEN NOT MATCHED THEN INSERT' option, that column must be taken from the source query only. It cannot be a hardcoded value.

Taking your eg:

MERGE  INTO T_T2 T2
USING  (
       select coalesce(T_T2.SKEY,-1) SKEY, T_T1.NKEY, T_T1.STRING1, T_T1.DATE1
       from   T_T1
       LEFT  
       JOIN   T_T2 on T_T2.NKEY = T_T1.NKEY
       --Subtract existing contents of T_T2 so we only pick up differences
       --We don't want to update every record that matches.
       minus
       select SKEY, NKEY, STRING1, DATE1 from T_T2
       ) T1
ON     T2.SKEY = T1.SKEY
WHEN   MATCHED THEN UPDATE
SET    STRING1      = T1.STRING1,
       DATE1        = T1.DATE1
WHEN   NOT MATCHED THEN INSERT (
       SKEY,
       NKEY,
       STRING1,
       DATE1
       )
values (
       T1.SKEY, --Taking SKEY from source query as it is a UPI
       T1.NKEY,
       T1.STRING1,
       T1.DATE1
       );

You will need to change the source query to generate SKEY accordingly.

Hope it helps. Thanks

S Gupta