Merge SQL Duplicate Key error

Database
Enthusiast

Merge SQL Duplicate Key error

I have two identical tables. One I treat as stagging (stg) and second as production (pr). I get a daily file which I first fastload into the stg table and then use the merge command to push data over to pr. I only have one same unique key defined on both tables (contact_object_id). Durring the merge sql run I get error message of primary key

Error: [Teradata Database] [TeraJDBC 14.00.00.21] [Error 2801] [SQLState 23000] Duplicate unique prime key error in DIRECT_pt_live.pt_optin_db.

SQLState:  23000

ErrorCode: 2801

This is not the first merge sql for me, I have other which works fine, but this time it is just not working.

Merge INTO DIRECT_pt_LIVE.pt_optin_db AS PR 

USING

(Select * From DIRECT_WRITER_LIVE.stg_optin_db) AS stg

 on (stg.contact_object_id = PR.contact_object_id)

WHEN MATCHED THEN UPDATE 

SET FIRSTNAME = stg.FIRSTNAME,

MIDDLENAME = stg.MIDDLENAME,

LASTNAME = stg.LASTNAME,

COMPANY_NAME = stg.COMPANY_NAME

WHEN NOT MATCHED THEN INSERT 

(stg.CONTACT_OBJECT_ID,stg.FIRSTNAME,stg.MIDDLENAME,stg.LASTNAME,stg.COMPANY_NAME);

stg and pr tables are multiset tables and they are both identical. 

CREATE MULTISET TABLE direct_pt_live.pt_optin_db ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CONTACT_OBJECT_ID DECIMAL(18,0),

      FIRSTNAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,

      MIDDLENAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,

      LASTNAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      COMPANY_NAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC

)

Unique Primary Index UPI_CONTACT_OBJECT_ID(CONTACT_OBJECT_ID);


do you know what I am doing wrong.

Tags (2)
7 REPLIES
WAQ
Enthusiast

Re: Merge SQL Duplicate Key error

One quick question, are you getting the same contact_object_id more than one in a particluar file?

WAQ
Enthusiast

Re: Merge SQL Duplicate Key error

I mean are you getting duplicate contact_object_id in a file?

Enthusiast

Re: Merge SQL Duplicate Key error

no..it is not possible. because stg table has the UPI on the contact_object_id.

Enthusiast

Re: Merge SQL Duplicate Key error

Looks like, at least one of the contact_object_id in the source file (stg) is already present in your target table (PR) (may be from previous loads). And as you have defined contact_object_id as UPI, it is not allowing to insert it again and throwing an error.

Enthusiast

Re: Merge SQL Duplicate Key error

Right..isn't merge suppose to use the "Update set" instead of inserting new record?

Enthusiast

Re: Merge SQL Duplicate Key error

Can you make sure that contact_object_id is unique in both stg and pr tables by taking count(*) and count(distinct contact_object_id) ? Because MULTISET and UPI together do not make sense to me. 

Enthusiast

Re: Merge SQL Duplicate Key error

I fixed the error message. Production table had null in the key field and merge was trying to insert another null instead of updating. I removed the null value from the production table and updated the merge command to select rows where contact_object_id is not null from the staging table.

Thanks KS.