All Forums Database
antoniovaldes 1 post Joined 12/15
02 Mar 2016
MERGE statement problem with IDENTITY field on TARGET Table

Hi all,
 
 I am facing some challenges with the MERGE statement.  I am trying to move data from a source table to a destintion table. 
 These are the DDLs of my two tables:

CREATE VOLATILE TABLE ZZ_SOURCE (
        TEST_NUM VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
        TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
        TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
    ) UNIQUE PRIMARY INDEX ("TEST_NUM", "TEST_COMMENT_TYPE_CD") ON COMMIT PRESERVE ROWS;

and

CREATE SET TABLE ODS_TABLES_TEST.TARGET, NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      TEST_COMMENT_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE -2147483647 
            MAXVALUE 2147483647 
            NO CYCLE),
      TEST_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      UPDATE_INTERFACE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      CREATE_DTTM TIMESTAMP(6) NOT NULL,
      UPDATE_DTTM TIMESTAMP(6))
PRIMARY INDEX ( TEST_COMMENT_ID );

So my 1st MERGE statement looked like this:

MERGE INTO "ODS_TABLES_TEST"."TARGET" tt
USING 
(
    SELECT 
        "TEST_NUM"
        ,"TEST_COMMENT_TYPE_CD"
        ,"TEST_COMMENT_TXT"
        ,'1048 TEST COMMENT' AS "UPDATE_INTERFACE_NM"
        ,CURRENT_TIMESTAMP AS "CREATE_DTTM"
        ,CURRENT_TIMESTAMP AS "UPDATE_DTTM"
    FROM
        "ZZ_SOURCE"
) ss 
ON (ss."TEST_NUM" = tt."TEST_NUM" AND ss."TEST_COMMENT_TYPE_CD" = tt."TEST_COMMENT_TYPE_CD")
WHEN MATCHED THEN UPDATE SET "TEST_COMMENT_TXT" = ss."TEST_COMMENT_TXT"
WHEN NOT MATCHED THEN INSERT ("TEST_NUM", "TEST_COMMENT_TYPE_CD", "TEST_COMMENT_TXT", "UPDATE_INTERFACE_NM", "CREATE_DTTM", "UPDATE_DTTM" ) 
                       values(ss."TEST_NUM", ss."TEST_COMMENT_TYPE_CD", ss."TEST_COMMENT_TXT", ss."UPDATE_INTERFACE_NM", ss."CREATE_DTTM", ss."UPDATE_DTTM");

and I got this error:
Failed [5758 : HY000] MyProcedure: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)
Because of this I modified my MERGE statement to look like this, to be able to use the TARGET primary index for the matching, but even this throws the same error

MERGE INTO "ODS_TABLES_TEST"."TARGET" tt
USING 
(
    SELECT
        t."TEST_COMMENT_ID"
        ,zz."TEST_NUM"
        ,zz."TEST_COMMENT_TYPE_CD"
        ,zz."TEST_COMMENT_TXT"
        ,'xxxxxxx xxxxx' AS "UPDATE_INTERFACE_NM"
        ,CURRENT_TIMESTAMP AS "CREATE_DTTM"
        ,CURRENT_TIMESTAMP AS "UPDATE_DTTM"
    FROM
        "ZZ_SOURCE" zz
        LEFT JOIN "ODS_TABLES_TEST"."TARGET" t ON
            zz."TEST_NUM" = t."TEST_NUM"
            AND zz."TEST_COMMENT_TYPE_CD" = t."TEST_COMMENT_TYPE_CD"
) ss 
ON (ss."TEST_COMMENT_ID" = tt."TEST_COMMENT_ID")
WHEN MATCHED THEN UPDATE SET "TEST_COMMENT_TXT" = ss."TEST_COMMENT_TXT"
WHEN NOT MATCHED THEN INSERT ( "TEST_NUM", "TEST_COMMENT_TYPE_CD", "TEST_COMMENT_TXT", "UPDATE_INTERFACE_NM", "CREATE_DTTM", "UPDATE_DTTM" ) 
                      values(ss."TEST_NUM", ss."TEST_COMMENT_TYPE_CD", ss."TEST_COMMENT_TXT", ss."UPDATE_INTERFACE_NM", ss."CREATE_DTTM", ss."UPDATE_DTTM");

 
Now if I change my target table DDL to be something like this: (Remove the identiy field and change the primary index)
 

CREATE SET TABLE ODS_TABLES_TEST.TARGET, NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      TEST_NUM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TEST_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TEST_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      UPDATE_INTERFACE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      CREATE_DTTM TIMESTAMP(6) NOT NULL,
      UPDATE_DTTM TIMESTAMP(6))
PRIMARY INDEX ( TEST_NUM , TEST_COMMENT_TYPE_CD );

And execute my first merge statement it will work fine. 
Now I am not suppose to modify the TARGET DDL just because of this tiny issue :P 

How can I make the MERGE statement to work when having an identity column?
 

Adeel Chaudhry 773 posts Joined 04/08
06 Mar 2016

The following should help resolving this issue:
 
https://forums.teradata.com/forum/database/using-merge-into-a-table-with-an-identity-column-result-failure-5758

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.